Това е глава от книгата: Майкъл Гървин. Ctrl+Shift+Enter. Овладяване на формули за масиви в Excel.

Проби, базирани на едно или повече условия.Редете Функции на Excelизползвайте оператори за сравнение. Например SUMISF, SUMIFS, COUNTIFS, COUNTIFS, AVERAGEIF и AVERAGEIFS. Тези функции правят избор въз основа на едно или повече условия (критерии). Проблемът е, че тези функции могат само да събират, броят и осредняват. Ами ако искате да наложите условия на търсенето, например максимална стойност или стандартно отклонение? В тези случаи, тъй като няма вградена функция, трябва да измислите формула за масив. Това често се дължи на използването на оператора за сравнение на масиви. Първият пример в тази глава показва как да се изчисли минималната стойност при едно условие.

Нека използваме функцията IF, за да изберем елементи от масива, които отговарят на условие. На фиг. 4.1 в лявата таблица има колона с имената на градовете и колона с времето. Трябва да намерите минималното време за всеки град и да поставите тази стойност в съответната клетка на дясната таблица. Условието за вземане на проби е името на града. Ако използвате функцията MIN, можете да намерите минималната стойност на колона B. Но как да изберете само онези числа, които са специфични за Оукланд? И как се копират формулите надолу по колоната? Тъй като Excel няма вградена функция MINIF, трябва да напишете оригинална формула, която комбинира функциите IF и MIN.

Ориз. 4.1. Цел на формулата: изберете минималното време за всеки град

Изтеглете бележката във формат или формат

Както е показано на фиг. 4.2, трябва да започнете да въвеждате формулата в клетка E3 с функцията MIN. Но не можете да водите спор номер 1всички стойности на колона B!? Искате да изберете само онези стойности, които са специфични за Окланд.

Както е показано на фиг. 4.3, в следващата стъпка въведете функцията IF като аргумент номер 1за МИН. Поставяте IF вътре в MIN.

Като поставите курсора там, където е въведен аргументът log_изразФункция IF (фиг. 4.4), избирате диапазона с имената на градовете A3:A8 и след това натиснете F4, за да направите препратките към клетките абсолютни (за повече подробности вижте например). След това въвеждате оператора за сравнение, знака за равенство. Накрая ще изберете клетката отляво на формулата - D3, като оставите препратката към нея относителна. Формулираното условие ще ви позволи да изберете само Auckland, когато разглеждате диапазона A3:A8.

Ориз. 4.4. Създайте оператор за масив в аргумент log_израз IF функции

Така че сте създали оператор за масив, използвайки оператора за сравнение. По всяко време по време на обработката на масива операторът на масива е оператор за сравнение, така че неговият резултат ще бъде масив, състоящ се от стойностите TRUE и FALSE. За да проверите това, изберете масива (за да направите това, щракнете върху аргумента в подсказката log_израз) и натиснете F9 (фиг. 4.5). Обикновено използвате един аргумент лог_израз,връща TRUE или FALSE; тук полученият масив ще върне множество TRUE и FALSE стойности, така че функцията MIN ще избере минималния брой само за тези градове, които съответстват на TRUE стойността.

Ориз. 4.5. За да видите масив, състоящ се от стойности TRUE и FALSE, щракнете върху аргумента в подсказката log_изрази натиснете F9

KPI е показател за ефективност, който ви позволява обективно да оцените ефективността на извършените действия. Тази система се използва за оценка на различни показатели (дейността на цялата компания, отделни структури, конкретни специалисти). Той не само изпълнява контролни функции, но и стимулира трудовата дейност. Често системата за възнаграждение се изгражда на базата на KPI. Това е техника за формиране на променливата част от работната заплата.

KPI ключови показатели за ефективност: примери в Excel

Стимулиращият фактор в системата за мотивация на KPI е паричното възнаграждение. Може да го получи служителят, който е изпълнил възложената му задача. Размерът на бонуса/бонуса зависи от представянето на конкретния служител в отчетен период. Размерът на възнаграждението може да бъде фиксиран или изразен като процент от заплатата.

Всяко предприятие определя ключовите показатели за ефективност и теглото на всяко поотделно. Данните зависят от целите на компанията. Например:

1. Целта е да се постигне план за продажби на продукти от 500 000 рубли месечно. Основният показател е планът за продажби. Система за измерване: действителна сума на продажбите / планирана сума на продажбите.
2. Целта е количеството на пратките за периода да се увеличи с 20%. Основният показател е средната сума на пратката. Система за измерване: действителна средна пратка / планирана средна пратка.
3. Целта е да се увеличи броят на клиентите с 15% в даден регион. Ключовият показател е броят на клиентите в корпоративната база данни. Система на измерване: действителен брой клиенти / планиран брой клиенти.

Предприятието също така определя независимо разпределението на коефициента (теглата). Например:

1. Изпълнение на плана под 80% е недопустимо.
2. Изпълнение на плана 100% - коефициент 0,45.
3. Изпълнение на плана 100-115% - коефициент 0,005 за всеки 5%.
4. Без грешки – коефициент 0,15.
5. През отчетния период няма коментари – коефициент 0,15.

Това е само възможен вариант за определяне на мотивационни коефициенти.

Ключовият момент при измерването на KPI е съотношението на действителния показател към планирания. Почти винаги заплатата на служителя се състои от заплата (фиксирана част) и бонус (променлива / променлива част). Коефициентът на мотивация влияе върху формирането на променливата.

Да приемем, че съотношението на постоянната и променливата част в заплатата е 50 × 50. Основни показатели за ефективност и тежестта на всеки от тях:

Нека приемем следните стойности на коефициента (еднакви за индикатор 1 и показател 2):

KPI таблица в Excel:

Обяснения:

Това е примерна таблица с KPI в Excel. Всяко предприятие създава свое собствено (като се вземат предвид характеристиките на работата и бонусната система).



KPI матрица и пример в Excel

За да се оценят служителите спрямо ключови показатели за ефективност, се изготвя матрица или споразумение за целите. Общата форма изглежда така:

1. Ключовите показатели са критериите, по които се оценява работата на персонала. Те са различни за всяка позиция.
2. Теглата са числа в диапазона от 0 до 1, общата сума на които е 1. Те ​​отразяват приоритетите на всеки ключов показател, като се вземат предвид целите на компанията.
3. База – приемлива минимална стойност на показателя. Под базовото ниво – без резултат.
4. Норма – планирано ниво. Нещо, което един служител трябва да направи. Отдолу - служителят не успя да се справи със задълженията си.
5. Целта е стойност, към която да се стремим. Надстандартен показател, който ви позволява да подобрите резултатите.
6. Факт – реални резултати от работата.
7. Индексът KPI показва нивото на резултатите спрямо нормата.

Формула за изчисляване на kpi:

Индекс на KPI = ((Действително - База) / (Норма - База)) * 100%.

Пример за попълване на матрица за офис мениджър:

Коефициентът на ефективност е сумата от произведенията на индекси и тегла. Оценките за представяне на служителите са ясно показани с помощта на условно форматиране.

Тема: Вземане на решения по няколко критериални показателя.

На практика обикновено се налага избор на управленско решение не по един критерий, а по няколко. Следователно техните стойности по време на сравнителната оценка са многопосочни, т.е. алтернативата печели по един показател, но губи по други.

При тези условия е необходимо да се намали разглежданата система от оценки на индикаторите до една цялостна система, въз основа на която ще се вземе решение.

За да се изгради цялостна оценка, трябва да се решат два проблема:

Първият проблем е, че разглежданите показатели на критериите са с различна важност;

Вторият проблем се характеризира с това, че показателите се оценяват в различни мерни единици и за изграждане на цялостна оценка е необходимо да се премине към един метър.

Първият проблем се решава чрез използване на една от четирите модификации на метода за експертна оценка, а именно методът на двойното сравнение, който позволява количествена оценка на значимостта. Същността на метода за сравняване на двойки е, че експерт (специалист, потенциален инвеститор, потребител) извършва оценка по двойки на разглежданите критериални показатели, определяйки за себе си степента им на важност под формата на резултат. След това, след подходяща обработка на получената информация, се изчислява коефициентът на значимост за всеки от разглежданите критериални показатели.

Вторият проблем се решава чрез използване на един измервателен уред за частни индикатори. Най-често като такъв измервателен уред се използва точков рейтинг. В този случай оценката се извършва по два подхода:

- първи подходизползва се при липса на статистически данни за стойността на разглежданите показатели;

- втори подходизползва се при наличие на статистически данни (граници на изменение) за стойността на разглежданите показатели.

Когато използвате първия подход за преобразуване в точки, продължете по следния начин: най-добрата стойност на разглеждания индикатор се приема равна на 1 точка, а най-лошите стойности се приемат като части от тази точка. Този подход е прост, дава обективна оценка, но в същото време не взема предвид най-добрите постижения, които са извън разглежданите варианти.

За да се премахне този недостатък, е необходима информация за границите на изменение на разглеждания индикатор. Ако е наличен, се използва вторият подход. В този случай се конструира скала за преобразуване, за да се преобразува в точки. В този случай системата за оценяване се избира, като се използват разпоредбите на статистическата теория съгласно формулата на Sturges:

н = 1 + 3,322 lg н , Където

N – брой статистически наблюдения;

n – приетата точкова система, получена с помощта на правила за закръгляване.

Преобразуването в точки се извършва на базата на изградената скала за преобразуване, като се използва процедурата за интерполация за таблични данни.

Упражнение:

От 6 алтернативни решения, всяко от които се оценява по 5 критериални показателя, е необходимо да се избере най-добрият вариант.

Извършете оценката, като използвате 2 подхода:

1) при липса на статистически данни за стойността на разглежданите показатели;

2) ако е наличен.

Границите за промени в показателите се установяват на базата на следния брой наблюдения (N):

За четни варианти N = 8;

Оценката на значимостта се извършва въз основа на двойни оценки по мнението на изпълнителя.

Маса 1.

Варианти на задачите

работа №	1	2	3	4	5
Брой алтернативи	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
работа №	6	7	8	9	10
Брой алтернативи	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
работа №	11	12	13	14	15
Брой алтернативи	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
работа №	16	17	18	19	20
Брой алтернативи	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
работа №	21	22	23	24	25
Брой алтернативи	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
работа №	26	27	28	29	30
Брой алтернативи	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

Таблица 2.

Първоначални данни:

№№	Алтернативни решения
показатели	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15
х 1	5	10	15	6	11	16	7	14	18	20	19	8	21	13	10
х 2	10	9	8	8	5	7	4	9	5	8	7	7	6	3	2
х 3	4	3	5	10	6	5	11	7	7	9	8	12	8	5	9
х 4	1	2	3	4	4	3	2	1	1	3	4	2	2	4	3
х 5	10	14	13	11	12	20	21	23	17	18	19	24	22	16	18

Таблица 3.

Пример:

Дадени са четири алтернативни решения, всяко от които е оценено по 5 критериални показателя. Въз основа на условията на задачата трябва да изберете най-добрия вариант.

На 1-ви етап е необходимо да се даде количествена оценка на значимостта на всеки показател. Използва се методът на двойното сравнение, който се базира на експертни оценки.

Въз основа на тази оценка се съставя матрична таблица и се изчислява коефициентът на значимост –Kзi.

Количествената оценка на значимостта на показателите се определя, както следва: ако при двойна оценка експерт (специалист, потенциален инвеститор, потребител) даде предпочитание на един от факторите, тогава номерът на фактора, който е бил предпочитан, се поставя в реда и колоната на матрицата за количествена оценка (вижте таблица 4). След това за всеки ред се определя броят на предпочитанията, дадени на един или друг фактор, с тяхната оценка по двойки и тяхната сума (Σпi). След това коефициентът на значимост се изчислява по следната формула:

Количествена оценка на значимостта на показателите:

Таблица 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

Първи подход.

Първият подход за преобразуване в точки се характеризира с факта, че най-добрата стойност на индикатора се приема равна на 1 точка, а най-лошата се оценява като част от тази точка. Този подход е прост, осигурява обективна сравнителна оценка, но взема предвид най-добрите постижения, които не са включени в сравнителните варианти.

Код на индикатора	Резултат в точки				Kзi	Оценка в точки, като се вземе предвид Kзi
	A1	A2	A3	A4		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
Цялостна оценка					0,725	0,532	0,717	0,73 4

Например: Х1А1: 6/20=0,3

Х2А1: 8/9=0.89

Заключение:Използвайки първия подход, най-добрият вариант сред алтернативите ще бъде вариант А4, тъй като има най-висока цялостна оценка. Следват опции A1, A3, A2.

Втори подход.

Елиминира недостатъците на първия подход, но използването му изисква информация за границите на изменение на разглеждания индикатор. В този случай се конструира скала за преобразуване, за да се преобразува в точки. Точковата система е избрана въз основа на принципите на статистическата теория и зависи от броя на наблюденията, използвани като основа за формиране на границите за промени в показателите.

Да приемем, че в нашия пример са извършени 8 наблюдения (N=8), което позволи да се установят следните граници за промени в показателите за качество (вижте таблица 3).

При наличие на тези показатели се изгражда скала за преобразуване в точки.

- Формула на Стърджс,

където N е броят на наблюденията.

Следователно показателят за качество ще се оценява по 4-точкова система, т.е. n = 4.

- диапазон на вариация,

където са максималните и минималните стойности от границите на промяна на индикатора i.

Стъпката на промяна на индикатора.

Скалата за преобразуване в точки е таблица, в която за всяка точка са посочени границите на изменение на показателите. При преобразуване на стойностите на индикатора в точки по дадена скала, ако стойността на индикатора е в интервала, тогава се използва процедурата за интерполиране на таблични данни.

Скалата за преобразуване в точки

След това показателите за качество на всички продукти се оценяват в точки. Например, според алтернатива A1: цифровата стойност на индикатора се взема от изходните данни, след което с помощта на скалата за преобразуване в точки се определя интервалът, в който тази стойност попада. След това се дава точкова оценка: долната граница на изменението на индикатора в даден интервал се изважда от числената стойност на индикатора, разделена на стъпка и се добавя предишният интервал. За индикаторите X4, X5 числовата стойност на индикатора се изважда от горната граница на изменение на индикатора в даден интервал, разделя се на стъпка и се добавя предишният интервал.

Получените стойности са обобщени в таблицата по-долу.

индикатор	Резултат в точки				Kзi	Оценка в точки, като се вземе предвид Kзi
	A1	A2	A3	A4		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
Цялостна оценка					1,973	1,432	2,149	2,334

Заключение:при втория подход най-добрият вариант сред алтернативите ще бъде вариант А4, тъй като той има най-добрата цялостна оценка. Следват опции A3, A2, A1.

Да приемем, че имате следния отчет за търговските представители:

От него трябва да разберете колко моливипродава се от търговски представител Иванов V януари.

ПРОБЛЕМ: Как да обобщим данните по няколко критерия??

РЕШЕНИЕ: Метод 1:

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

В английската версия:

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

КАК РАБОТИ:

От базата данни, която посочихме A1: G16функция BDSUMMизвлича и обобщава данни от колони Количество(аргумент " Поле" = F1) според посочените в клетките I1:K2 (Продавач = Иванов; Продукти = Моливи;Месец = януари) критерии.

ПРОТИВ: Списъкът с критерии трябва да е на листа.

БЕЛЕЖКИ: Броят на критериите за сумиране е ограничен от RAM.

ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel

Метод 2:

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

В английската версия:

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

КАК РАБОТИ:

Функцията SUMPRODUCT формира масиви от TRUE и FALSE стойности, според избраните критерии, в паметта на Excel.

Ако изчисленията бяха извършени в клетките на листа (за по-голяма яснота, ще демонстрирам цялата работа на формулата, сякаш изчисленията се случват на листа, а не в паметта), тогава масивите ще изглеждат така:

Очевидно е, че ако напр. D2=Моливи, тогава стойността ще бъде TRUE и ако D3=Папки, след това FALSE (тъй като критерият за избор на продукт в нашия пример е стойността Моливи).

Знаейки, че стойността TRUE винаги е равна на 1, а FALSE винаги е равна на 0, ние продължаваме да работим с масиви, както с числата 0 и 1.
Чрез умножаване на получените стойности на масива помежду си последователно, получаваме ЕДИН масив от нули и единици. Когато са изпълнени и трите критерия за подбор, ( ИВАНОВ, МОЛИВ, ЯНУАРИ) т.е. всички условия взеха стойностите TRUE, получаваме 1 (1*1*1 = 1), но ако поне едно условие не е изпълнено, получаваме 0 (1*1*0 = 0; 1*0*1 = 0; 0*1* 1 = 0).

Сега всичко, което остава, е да умножим получения масив по масив, съдържащ данните, които трябва да сумираме в крайна сметка (диапазон F2:F16) и всъщност сумират това, което не е умножено по 0.

Сега сравнете масивите, получени с помощта на формулата и с изчислението стъпка по стъпка на листа (маркиран в червено).

Мисля че всичко е ясно :)

МИНУСИ: SUMPRODUCT - формула за "тежък" масив. При изчисляване на големи диапазони от данни времето за преизчисляване се увеличава значително.

БЕЛЕЖКИ

ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel

Метод 3: Формула за масив

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

В английската версия:

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

КАК РАБОТИ:Точно същото като метод №2. Има само две разлики - тази формула се въвежда с натискане Ctrl+Shift+Enter, а не само с натискане Въведетеи масивът от 0 и 1 не се умножава по обхвата на сумиране, а се избира с помощта на функцията IF.

МИНУСИ: Формулите за масиви при изчисляване на големи диапазони от данни значително увеличават времето за преизчисляване.

БЕЛЕЖКИ: Броят на обработените масиви е ограничен до 255.

ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel

Метод 4:

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

Условно форматиране (5)
Списъци и диапазони (5)
Макроси (VBA процедури) (63)
Разни (39)
Грешки и проблеми в Excel (4)

VPR въз основа на два или повече критерия

Със сигурност всеки, който е запознат с Функция VLOOKUPзнайте, че търси определени стойности изключително в лявата колона на посочената таблица (можете да прочетете повече за VLOOKUP в статията: Как да намерите стойност в друга таблица или VLOOKUP сила). Освен това много хора знаят, че VLOOKUP търси само на базата на една стойност.

Помогна ли статията? Споделете връзката с приятелите си! Видео уроци

("Долна лента":("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":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"слайд","texteffectslidedirection2":"надясно","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss:"text-align:left;","textbgcs":"absolute; background-color:#333333; filter:alpha(opacity=60); ","titlecss":"display:block; позиция: роднина; шрифт: удебелен 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; цвят:#fff;","descriptioncss":"дисплей:блок; позиция: роднина; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; цвят:#fff; margin-top:8px;","buttoncss":"display:block; позиция: роднина; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Подобни статии