Det här är ett kapitel ur boken: Michael Girvin. Ctrl+Skift+Enter. Bemästra matrisformler i Excel.

Prover baserade på ett eller flera villkor. Rad Excel-funktioner använd jämförelseoperatorer. Till exempel SUMIF, SUMIFS, COUNTIFS, COUNTIFS, AVERAGEIF och AVERAGEIFS. Dessa funktioner gör val baserat på ett eller flera villkor (kriterier). Problemet är att dessa funktioner bara kan lägga till, räkna och genomsnitt. Vad händer om du vill ställa villkor för sökningen, till exempel maximalt värde eller standardavvikelse? I dessa fall, eftersom det inte finns någon inbyggd funktion, måste du uppfinna en matrisformel. Detta beror ofta på användningen av array-jämförelseoperatorn. Det första exemplet i detta kapitel visar hur man beräknar minimivärdet givet ett villkor.

Låt oss använda IF-funktionen för att välja arrayelement som uppfyller ett villkor. I fig. 4.1 i den vänstra tabellen finns en kolumn med namn på städer och en kolumn med tid. Du måste hitta minimitiden för varje stad och placera detta värde i motsvarande cell i den högra tabellen. Provtagningsvillkoret är namnet på staden. Om du använder MIN-funktionen kan du hitta minimivärdet för kolumn B. Men hur väljer du bara de siffror som är specifika för Auckland? Och hur kopierar man formlerna ner i kolumnen? Eftersom Excel inte har en inbyggd MINESLI-funktion måste du skriva en originalformel som kombinerar IF- och MIN-funktionerna.

Ris. 4.1. Syftet med formeln är att välja minimitiden för varje stad

Ladda ner anteckningen i format eller format

Som visas i fig. 4.2 bör du börja skriva in formeln i cell E3 med MIN-funktionen. Men du kan inte argumentera nummer 1 alla värden i kolumn B!? Du vill bara välja de värden som är specifika för Auckland.

Som visas i fig. 4.3, i nästa steg anger du IF-funktionen som ett argument nummer 1 för MIN. Du lägger OM inom MIN.

Genom att placera markören där argumentet matas in log_expression IF-funktionen (Fig. 4.4), väljer du området med namnen på städerna A3:A8 och trycker sedan på F4 för att göra cellreferenserna absoluta (för mer information, se t.ex.). Sedan skriver du den jämförande operatorn, likhetstecknet. Slutligen väljer du cellen till vänster om formeln - D3, och lämnar referensen till den relativ. Det formulerade villkoret gör att du endast kan välja Auckland när du tittar på området A3:A8.

Ris. 4.4. Skapa en matrisoperator i ett argument log_expression IF-funktioner

Så du har skapat en matrisoperator med jämförelseoperatorn. När som helst under arraybehandling är arrayoperatorn en jämförelseoperator, så resultatet blir en array som består av värdena TRUE och FALSE. För att verifiera detta, välj arrayen (för att göra detta, klicka på argumentet i verktygstipset log_expression) och tryck på F9 (Fig. 4.5). Vanligtvis använder du ett argument log_expression, returnerar antingen TRUE eller FALSE; här kommer den resulterande arrayen att returnera flera TRUE- och FALSE-värden, så MIN-funktionen kommer att välja det minsta antalet endast för de städer som matchar TRUE-värdet.

Ris. 4.5. Klicka på argumentet i verktygstipset för att se en array som består av TRUE och FALSE-värden log_expression och tryck på F9

KPI är en prestationsindikator som låter dig objektivt bedöma effektiviteten av utförda åtgärder. Detta system används för att utvärdera olika indikatorer (hela företagets verksamhet, enskilda strukturer, specifika specialister). Den utför inte bara kontrollfunktioner utan stimulerar också arbetsaktivitet. Ofta byggs ett ersättningssystem upp utifrån nyckeltal. Detta är en teknik för att bilda den rörliga delen av lönen.

KPI nyckeltal: exempel i Excel

Den stimulerande faktorn i KPI-motivationssystemet är monetär belöning. Den kan tas emot av den anställde som har slutfört den uppgift som tilldelats honom. Storleken på utmärkelsen/bonusen beror på en viss anställds prestation inom rapporteringsperiod. Ersättningens storlek kan vara fast eller uttryckt i procent av lönen.

Varje företag bestämmer nyckeltal och vikten av varje individuellt. Uppgifterna beror på företagets mål. Till exempel:

Målet är att säkerställa en produktförsäljningsplan på 500 000 rubel varje månad. Nyckelindikatorn är försäljningsplanen. Mätsystem: faktisk försäljningsbelopp / planerat försäljningsbelopp.
Målet är att öka mängden försändelser under perioden med 20 %. Nyckelindikatorn är det genomsnittliga leveransbeloppet. Mätsystem: faktisk genomsnittlig leverans / planerad genomsnittlig leverans.
Målet är att öka antalet kunder med 15 % i en viss region. Nyckelindikatorn är antalet kunder i företagsdatabasen. Mätsystem: faktiskt antal kunder / planerat antal kunder.

Företaget bestämmer också spridningen av koefficienten (vikterna) oberoende. Till exempel:

Uppfyllelse av planen med mindre än 80 % är oacceptabelt.
Planuppfyllelse 100% - koefficient 0,45.
Planuppfyllelse 100-115 % - koefficient 0,005 för varje 5 %.
Inga fel – koefficient 0,15.
Det har inte lämnats några kommentarer under rapportperioden – koefficient 0,15.

Detta är bara ett möjligt alternativ för att bestämma motivationskoefficienter.

Nyckelpunkten för att mäta KPI är förhållandet mellan den faktiska indikatorn och den planerade. Nästan alltid består en anställds lön av en lön (fast del) och en bonus (rörlig/rörlig del). Motivationskoefficienten påverkar bildningen av variabeln.

Låt oss anta att förhållandet mellan de konstanta och rörliga delarna i lönen är 50 × 50. Nyckeltal och vikten av var och en av dem:

Låt oss acceptera följande koefficientvärden (samma för indikator 1 och indikator 2):

KPI-tabell i Excel:

Förklaringar:

Detta är ett exempel på KPI-tabell i Excel. Varje företag utgör sitt eget (med hänsyn till arbetets egenskaper och bonussystemet).

KPI-matris och exempel i Excel

För att utvärdera medarbetarna mot nyckeltal görs en matris eller överenskommelse om mål. Den allmänna formen ser ut så här:

Nyckelindikatorer är kriterierna för bedömning av personalens arbete. De är olika för varje position.
Vikter är siffror i intervallet från 0 till 1, vars totala summa är 1. De speglar prioriteringarna för varje nyckelindikator, med hänsyn till företagets mål.
Bas – acceptabelt lägsta värde för indikatorn. Under grundnivån – inget resultat.
Norm – planerad nivå. Något som en anställd måste göra. Nedan - den anställde misslyckades med sina arbetsuppgifter.
Ett mål är ett värde att sträva efter. En indikator över standard som låter dig förbättra resultaten.
Fakta – faktiska resultat av arbetet.
KPI-index visar resultatnivån i förhållande till normen.

Formel för att beräkna kpi:

KPI-index = ((Faktisk - Bas) / (Norm - Bas)) * 100 %.

Ett exempel på att fylla i en matris för en kontorschef:

Prestationskoefficienten är summan av produkterna av index och vikter. Anställdas prestationsbetyg visas tydligt med villkorlig formatering.

Ämne: Beslutsfattande baserat på flera kriterieindikatorer.

I praktiken är det vanligtvis nödvändigt att välja ett förvaltningsbeslut inte efter ett kriterium, utan enligt flera. Därför är deras värden under jämförande bedömning flerriktade, dvs. alternativet vinner med ett mått, men förlorar med andra.

Under dessa förutsättningar är det nödvändigt att reducera systemet med indikatorbedömningar som övervägs till ett heltäckande system, på grundval av vilket ett beslut kommer att fattas.

För att bygga en heltäckande bedömning måste två problem lösas:

Det första problemet är att de kriteriumindikatorer som övervägs är av olika betydelse;

Det andra problemet kännetecknas av det faktum att indikatorer bedöms i olika måttenheter och för att bygga en heltäckande bedömning är det nödvändigt att flytta till en enda meter.

Det första problemet löses genom att använda en av fyra modifieringar av expertbedömningsmetoden, nämligen den parade jämförelsemetoden, som möjliggör en kvantitativ bedömning av signifikans. Kärnan i den parade jämförelsemetoden är att en expert (specialist, potentiell investerare, konsument) gör en parvis bedömning av de kriteriumindikatorer som övervägs och själv bestämmer deras grad av betydelse i form av en poängpoäng. Efter detta, efter lämplig bearbetning av den mottagna informationen, beräknas signifikanskoefficienten för var och en av de aktuella kriteriumindikatorerna.

Det andra problemet löses genom att använda en enda mätare för privata indikatorer. Oftast används en poängbetyg som en sådan mätare. I det här fallet utförs bedömningen med två metoder:

- första tillvägagångssättet används i avsaknad av statistiska uppgifter om värdet av de indikatorer som övervägs.

- andra tillvägagångssättet används i närvaro av statistiska uppgifter (gränser för förändring) om värdet av de indikatorer som övervägs.

När du använder det första tillvägagångssättet för att konvertera till poäng, fortsätt enligt följande: det bästa värdet på den aktuella indikatorn tas lika med 1 poäng, och de sämsta värdena tas som bråkdelar av denna punkt. Detta tillvägagångssätt är enkelt, ger en objektiv bedömning, men tar samtidigt inte hänsyn till de bästa prestationerna som ligger utanför de alternativ som övervägs.

För att eliminera denna nackdel behövs information om gränserna för förändring i den aktuella indikatorn. Om tillgängligt används den andra metoden. I det här fallet är en omvandlingsskala konstruerad för att omvandla till poäng. I det här fallet väljs poängsystemet med hjälp av bestämmelserna i statistisk teori enligt Sturges formel:

n = 1 + 3,322 lg N , Var

N – antal statistiska observationer;

n – det accepterade poängsystemet som erhålls med avrundningsregler.

Omvandling till poäng utförs på basis av den konstruerade omvandlingsskalan med hjälp av interpolationsproceduren för tabelldata.

Träning:

Från 6 alternativa lösningar, som var och en bedöms utifrån 5 kriterier, måste du välja det bästa alternativet.

Utför bedömningen med två metoder:

1) i avsaknad av statistiska uppgifter om värdet av de indikatorer som övervägs;

2) om tillgängligt.

Gränserna för förändringar i indikatorer fastställs utifrån följande antal observationer (N):

För jämna alternativ N = 8;

Betydelsesbedömningen görs på basis av parade bedömningar enligt utförarens uppfattning.

Bord 1.

Uppgiftsalternativ

Jobb Nej.	1	2	3	4	5
Antal alternativ	1,2,3,4,5,6	2,4,8,9,11,15	1,3,5,7,9,10	4,6,8,12,13,14	1,5,10,11,12,15
Jobb Nej.	6	7	8	9	10
Antal alternativ	6,7,10,11,14,15	3,4,5,8,9,10	7,8,9,10,13,15	1,2,3,13,14,15	2,4,5,7,12,13
Jobb Nej.	11	12	13	14	15
Antal alternativ	1,7,8,9,10,11	6,9,12,13,14,15	2,5,7,9,10,11	7,8,9,10,11,12	1,2,3,4,8,9
Jobb Nej.	16	17	18	19	20
Antal alternativ	1,2,3,10,12,13	2,5,7,8,10,15	1,6,7,12,13,14	3,4,5,6,10,14	2,8,11,12,14,15
Jobb Nej.	21	22	23	24	25
Antal alternativ	1,2,6,7,9,10	3,5,8,9,13,14	4,7,8,10,11,12	5,6,7,8,11,13	8,9,10,11,12,13
Jobb Nej.	26	27	28	29	30
Antal alternativ	1,3,4,10,11,15	2,3,5,8,9,15	1,4,7,11,13,15	2,6,7,8,12,14	1,10,11,12,8,4

Tabell 2.

Inledande data:

№№	Alternativa lösningar
indikatorer	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15
X 1	5	10	15	6	11	16	7	14	18	20	19	8	21	13	10
X 2	10	9	8	8	5	7	4	9	5	8	7	7	6	3	2
X 3	4	3	5	10	6	5	11	7	7	9	8	12	8	5	9
X 4	1	2	3	4	4	3	2	1	1	3	4	2	2	4	3
X 5	10	14	13	11	12	20	21	23	17	18	19	24	22	16	18

Tabell 3.

Exempel:

Fyra alternativa lösningar ges, som var och en bedöms utifrån 5 kriterier. Baserat på förutsättningarna för uppgiften måste du välja det bästa alternativet.

I det första steget är det nödvändigt att ge en kvantitativ bedömning av betydelsen av varje indikator. Den parade jämförelsemetoden används, som bygger på expertbedömningar.

Utifrån denna bedömning sammanställs en matristabell och signifikanskoefficienten –Kзi beräknas.

En kvantitativ bedömning av indikatorernas betydelse bestäms enligt följande: om, i en parad bedömning, en expert (specialist, potentiell investerare, konsument) gav företräde åt en av faktorerna, placeras numret på den faktor som föredrogs i raden och kolumnen i den kvantitativa bedömningsmatrisen (se tabell 4). Därefter bestäms för varje rad antalet preferenser som ges till en eller annan faktor med deras parvisa bedömning och deras summa (Σпi). Signifikanskoefficienten beräknas sedan med följande formel:

Kvantitativ bedömning av indikatorernas betydelse:

Tabell 4

X1	X2	X3	X4	X5	ΣPi		Kзi
X1	1	1	3	1	5	3		0,2
X2	1	2	2	2	5	3		0,2
X3	3	2	3	4	5	2		0,133
X4	1	2	4	4	5	2		0,133
X5	5	5	5	5	5	5		0,333
∑∑Пi					15	1

Första tillvägagångssättet.

Det första tillvägagångssättet för att konvertera till poäng kännetecknas av det faktum att det bästa värdet på indikatorn tas lika med 1 poäng, det sämsta uppskattas som en bråkdel av denna punkt. Detta tillvägagångssätt är enkelt, ger en objektiv jämförande bedömning, men tar hänsyn till de bästa prestationerna som inte ingår i de jämförande alternativen.

Indikatorkod	Gör poäng i poäng				Kзi	Få poäng med hänsyn till Kзi
Indikatorkod	A1	A2	A3	A4	Kзi	A1	A2	A3	A4
X1	0,3	0,35	0,7	1	0,2	0,06	0,07	0,14	0,2
X2	0,89	0,45	1	0,89	0,2	0,178	0,09	0,2	0,178
X3	0,91	1	0,64	0,82	0,133	0,121	0,133	0,085	0,110
X4	0,25	0,5	1	0,33	0,133	0,033	0,066	0,133	0,043
X5	1	0,52	0,48	0,61	0,333	0,333	0,173	0,159	0,203
Omfattande bedömning					0,725	0,532	0,717	0,73 4

Till exempel: Х1А1: 6/20=0,3

Х2А1: 8/9=0,89

Slutsats: Med det första tillvägagångssättet kommer det bästa alternativet bland alternativen att vara alternativ A4, eftersom det har den största heltäckande bedömningen. Nästa är alternativ A1, A3, A2.

Andra tillvägagångssättet.

Eliminerar nackdelarna med det första tillvägagångssättet, men dess användning kräver information om gränserna för förändring i den aktuella indikatorn. I det här fallet är en omvandlingsskala konstruerad för att omvandla till poäng. Poängsystemet väljs utifrån statistisk teoris principer och beror på antalet observationer som används som grund för bildandet av gränserna för förändringar av indikatorer.

Låt oss anta att i vårt exempel gjordes 8 observationer (N=8), vilket gjorde det möjligt att fastställa följande gränser för förändringar i kvalitetsindikatorer (se tabell 3).

Om dessa indikatorer finns tillgängliga, konstrueras en skala för omvandling till poäng.

- Sturges formel,

där N är antalet observationer.

Följaktligen kommer kvalitetsindikatorn att bedömas med hjälp av ett 4-punktssystem, d.v.s. n = 4.

- variationsbredd,

var är de högsta och lägsta värdena från gränserna för förändring av i-indikatorn.

Steget att ändra indikatorn.

Skalan för omvandling till poäng är en tabell där gränserna för förändringar i indikatorer anges för varje punkt. Vid konvertering av indikatorvärden till punkter på en given skala, om indikatorvärdet ligger inom intervallet, används proceduren för att interpolera tabelldata.

Omvandlingsskala till poäng

Därefter bedöms kvalitetsindikatorerna för alla produkter i poäng. Till exempel, enligt alternativ A1: det numeriska värdet för indikatorn tas från källdata, och sedan med hjälp av skalan för omvandling till poäng bestäms intervallet där detta värde faller. Sedan ges en punktuppskattning: den nedre gränsen för förändringen i indikatorn i ett givet intervall subtraheras från indikatorns numeriska värde, dividerat med ett steg och det föregående intervallet läggs till. För indikatorerna X4, X5 subtraheras indikatorns numeriska värde från den övre gränsen för förändring i indikatorn i ett givet intervall, dividerat med ett steg och det föregående intervallet läggs till.

De erhållna värdena sammanfattas i tabellen nedan.

indikator	Gör poäng i poäng				Kзi	Få poäng med hänsyn till Kзi
indikator	A1	A2	A3	A4	Kзi	A1	A2	A3	A4
X1	0,2	0,4	1,8	3	0,2	0,04	0,08	0,36	0,6
X2	3	1	3,5	3	0,2	0,6	0,2	0,7	0,6
X3	2,33	2,66	1,33	2	0,134	0,313	0,357	0,179	0,268
X4	0	2,34	4	1,67	0,134	0	0,314	0,536	0,224
X5	3,04	1,44	1,12	1,92	0,334	1,02	0,481	0,374	0,642
Omfattande bedömning					1,973	1,432	2,149	2,334

Slutsats: Med den andra metoden kommer det bästa alternativet bland alternativen att vara alternativ A4, eftersom det har den största omfattande bedömningen. Nästa är alternativ A3, A2, A1.

Låt oss anta att du har följande rapport om säljare:

Från det måste du ta reda på hur mycket pennor säljs av säljare Ivanov V januari.

PROBLEM: Hur sammanfattar man data enligt flera kriterier??

LÖSNING: Metod 1:

BDSUMM(A1:G16;F1;I1:K2)

I den engelska versionen:

DSUM(A1:G16;F1;I1:K2)

HUR DET FUNGERAR:

Från databasen vi angav A1:G16 fungera BDSUMM hämtar och sammanfattar kolumndata Kvantitet(argument" Fält" = F1) enligt de som anges i cellerna I1:K2 (Säljare = Ivanov; Produkter = Pennor;Månad = januari) kriterier.

NACKDELAR: Listan över kriterier bör finnas på bladet.

ANMÄRKNINGAR: Antalet summeringskriterier begränsas av RAM.

APPLIKATIONSOMRÅDE: Alla versioner av Excel

Metod 2:

SUMMAPRODUKT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

I den engelska versionen:

SUMMAPRODUKT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

HUR DET FUNGERAR:

SUMMAPRODUKT-funktionen bildar matriser av SANT och FALSKT värden, enligt de valda kriterierna, i Excel-minnet.

Om beräkningarna utfördes i arkcellerna (för tydlighetens skull kommer jag att demonstrera hela operationen av formeln som om beräkningarna ägde rum på arket och inte i minnet), då skulle matriserna se ut så här:

Det är uppenbart att om t.ex. D2=Blyertspennor, då blir värdet TRUE, och om D3=mappar, sedan FALSE (eftersom kriteriet för att välja en produkt i vårt exempel är värdet Pennor).

När vi vet att värdet TRUE alltid är lika med 1, och FALSE alltid är lika med 0, fortsätter vi att arbeta med arrayer som med siffrorna 0 och 1.
Genom att multiplicera de resulterande matrisvärdena sinsemellan sekventiellt får vi EN matris med nollor och ettor. När alla tre urvalskriterierna var uppfyllda, ( IVANOV, PENNA, JANUARI) dvs. alla villkor tog värdet TRUE, vi får 1 (1*1*1 = 1), men om åtminstone ett villkor inte var uppfyllt får vi 0 (1*1*0 = 0; 1*0*1 = 0; 0*1*1 = 0).

Nu återstår bara att multiplicera den resulterande arrayen med en array som innehåller de data som vi behöver för att slutligen summera (intervall F2:F16) och faktiskt summera det som inte multiplicerades med 0.

Jämför nu de arrayer som erhållits med formeln och med steg-för-steg-beräkningen på arket (markerat i rött).

Jag tror att allt är klart :)

MINUS: SUMPRODUKT - "tung" arrayformel. Vid beräkning på stora dataområden ökar omräkningstiden märkbart.

ANMÄRKNINGAR

APPLIKATIONSOMRÅDE: Alla versioner av Excel

Metod 3: Matrisformel

SUMMA(OM((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

I den engelska versionen:

SUMMA(OM((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

HUR DET FUNGERAR: Exakt samma som metod nr 2. Det finns bara två skillnader - denna formel skrivs in genom att trycka på Ctrl+Skift+Enter, och inte bara genom att trycka på Stiga på och matrisen av nollor och ettor multipliceras inte med summeringsintervallet, utan väljs med hjälp av IF-funktionen.

MINUS: Matrisformler vid beräkning på stora dataintervall ökar märkbart omräkningstiden.

ANMÄRKNINGAR: Antalet bearbetade arrayer är begränsat till 255.

APPLIKATIONSOMRÅDE: Alla versioner av Excel

Metod 4:

SUMMER(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)

Villkorlig formatering (5)
Listor och intervall (5)
Makron (VBA-procedurer) (63)
Övrigt (39)
Excel-buggar och fel (4)

VPR baserat på två eller flera kriterier

Säkert alla som är bekanta med VLOOKUP-funktion vet att den söker efter specificerade värden exklusivt i den vänstra kolumnen i den angivna tabellen (du kan läsa mer om VLOOKUP i artikeln: Hur man hittar ett värde i en annan tabell eller VLOOKUP-styrka). Många vet också att VLOOKUP endast söker på grundval av ett värde.

Hjälpte artikeln? Dela länken med dina vänner! Videolektioner
("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"vänster","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"text-align:left;","textbgcss":"display:absolute:#333333; ","titlecss":"display:block; position:släkting; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:släkting; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; färg:#fff; margin-top:8px;","buttoncss":"display:block; position:släkting; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))