Разделы сайта
Выбор редакции:
- Вертикальный конгломерат
- Фотограф Всеволод Тарасевич: сумасшедшая жизнь от «Формирования интеллекта» и до «Края земли
- Требуется продавец-консультант?
- «Полная неожиданность»: в России рухнули продажи электроники
- На слонимщине перерисовали соломенные фигуры, так как они уж очень напоминали известных людей беларуси
- Трудовая мотивация и удовлетворенность трудом Похожие работы на - Профессиональное удовлетворение работой разными поколениями сотрудн
- Как получить грант на начало бизнеса, руководство от первого лица
- Разделение рабочего времени на части
- Презентация на английском языке И
- Как формировать профили должностей для поиска ценных сотрудников?
Реклама
Осуществляться сразу по нескольким критериям. Сравнительный анализ: понятие, виды и примеры |
Предположим что у Вас есть вот такой отчёт по продажам торговых представителей: Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе . ПРОБЛЕМА : Как суммировать данные по нескольким критериям?? РЕШЕНИЕ
: Способ 1: БДСУММ(A1:G16;F1;I1:K2) В английской версии: DSUM(A1:G16,F1,I1:K2) КАК ЭТО РАБОТАЕТ: Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле " = F1 ) по заданным в ячейках I1:K2 (Продавец = Иванов ; Продукция = Карандаши ; Месяц = Январь ) критериям. МИНУСЫ : Список критериев должен быть на листе.
ПРИМЕЧАНИЯ
: Количество критериев суммирования ограничено оперативной памятью. Способ 2: СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16) В английской версии: SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16) КАК ЭТО РАБОТАЕТ: Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel. Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так: Очевидно что если например, D2=Карандаши , то значение будет равно ИСТИНА, а если D3=Папки , то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши ). Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1. Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать (диапазон F2:F16 ) и собственно, просуммировать то что на 0 не умножилось. Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным). Думаю всё понятно:) МИНУСЫ : СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта. ПРИМЕЧАНИЯ ОБЛАСТЬ ПРИМЕНЕНИЯ : Любая версия Excel Способ 3: Формула массива СУММ(ЕСЛИ((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 , а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ. МИНУСЫ : Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.
ПРИМЕЧАНИЯ
: Количество обрабатываемых массивов ограничено 255. Способ 4: СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2) Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel. Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии. Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН. Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города Скачать заметку в формате или в формате Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду. Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН. Разместив курсор в месте введения аргумента лог_выражение функции ЕСЛИ (рис. 4.4), вы выделяете диапазон с названиями городов А3:А8, а затем нажимаете F4, чтобы сделать ссылки на ячейки абсолютными (подробнее см., например, ). Затем вы набираете сравнительный оператор – знак равенства. Наконец, вы выделите ячейку слева от формулы – D3, оставляя ссылку на нее относительной. Сформулированное условие позволит выбрать только Окленды при просмотре диапазона А3:А8. Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ Итак, вы создали оператор массива с помощью оператора сравнения. В любой момент обработки массива оператор массива является оператором сравнения, так что результатом его работы будет массив, состоящий из значений ИСТИНА и ЛОЖЬ. Чтобы убедиться в этом, выделите массив (для этого щелкните во всплывающей подсказке на аргумент лог_выражение ) и нажмите F9 (рис. 4.5). Обычно вы используете один аргумент лог_выражение, возвращающее либо ИСТИНУ, либо ЛОЖЬ; здесь же результирующий массив вернет несколько значений ИСТИНЫ и ЛЖИ, так что функция МИН выберет минимальное число только для тех городов, которые соответствуют значению ИСТИНА. Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9 Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (63) Разное (39) Баги и глюки Excel (4) ВПР по двум и более критериямНаверняка все, кто знаком с функцией ВПР знают, что она осуществляет поиск заданных значений исключительно в левом столбце указанной таблицы(подробнее про ВПР можно прочитать в статье: Как найти значение в другой таблице или сила ВПР). Так же многие знают, что ВПР ищет только на основании одного значения. Статья помогла? Поделись ссылкой с друзьями! Видеоуроки KPI – показатель эффективности, позволяющий объективно оценить результативность выполняемых действий. Данная система применяется для оценки различных показателей (деятельности всей компании, отдельных структур, конкретных специалистов). Она выполняет не только функции контроля, но и стимулирует трудовую активность. Часто на основе KPI строится система оплаты труда. Это методика формирования переменной части зарплаты. KPI ключевые показатели эффективности: примеры в ExcelСтимулирующий фактор в системе мотивации KPI – денежное вознаграждение. Получить его может тот работник, который выполнил поставленную перед ним задачу. Сумма премии / бонуса зависит от результата конкретного сотрудника в отчетном периоде. Объем вознаграждения может быть фиксированным или выражаться в процентах к окладу. Каждое предприятие определяет ключевые показатели эффективности и вес каждого индивидуально. Данные зависят от задач компании. Например:
Разброс коэффициента (весы) предприятие также определяет самостоятельно. Например:
Это лишь возможный вариант определения мотивационных коэффициентов. Ключевой момент в измерении KPI – отношение фактического показателя к плановому. Практически всегда заработная плата сотрудника складывается из оклада (постоянной части) и премии (переменной / изменяемой части). Мотивационный коэффициент влияет на формирование переменной. Предположим, что соотношение постоянной и изменяемой частей в зарплате – 50 × 50. Ключевые показатели эффективности и вес каждого из них: Примем следующие значения коэффициентов (одинаковые для показателя 1 и показателя 2): Таблица KPI в Excel: Пояснения: Это примерная таблица KPI в Excel. Каждое предприятие составляет собственную (с учетом особенностей работы и системы премирования). Матрица KPI и пример в ExcelДля оценки работников по ключевым показателям эффективности составляется матрица, или соглашение о целях. Общая форма выглядит так:
Формула расчета kpi: Индекс KPI = ((Факт – База) / (Норма – База)) * 100%. Пример заполнения матрицы для офис-менеджера: Коэффициент результативности – сумма произведений индексов и весов. Оценка эффективности сотрудника наглядно показана с помощью условного форматирования. Предположим, что требуется суммировать значения с более чем одним условием, например сумма продаж продукта в определенном регионе. Это хороший случай использования функции СУММЕСЛИМН в формуле. Обратите внимание на этот пример, в котором у нас есть два условия: требуется сумма продаж мясо (из столбца C) в Южной области (из столбца a). Вот формула, которую можно использовать для акомплиш: = СУММЕСЛИМН (D2: D11; a2: A11; "Юг"; C2: C11; "мясо") Результат - значение 14 719. Рассмотрим более подробное представление каждой части формулы. СУММЕСЛИМН - это арифметическая формула. Вычисляет числа, которые в данном случае находятся в столбце D. Первый шаг - указать расположение чисел. =СУММЕСЛИМН(D2:D11, Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Этот диапазон ячеек - первый аргумент в этой формуле - первый элемент данных, который функция использует в качестве ввода. Затем нужно найти данные, отвечающие двум условиям, и ввести первое условие, указав для функции расположение данных (a2: A11), а также то, что такое условие - "Юг". Обратите внимание запятые между отдельными аргументами. =СУММЕСЛИМН(D2:D11;A2:A11;"Южный"; Кавычки вокруг слова "Юг" определяют, что эти текстовые данные. Наконец, вы вводите аргументы для второго условия - диапазон ячеек (C2:C11), которые содержат слово "Мясо", а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. Завершите формулу, закрыв закрывающая круглая скобка ) , а затем нажмите клавишу ВВОД. Результат - еще раз в 14 719. =СУММЕСЛИМН(D2:D11;A2:A11,"Южный";C2:C11,"Мясо") По мере ввода функции СУММЕСЛИМН в Excel, если вы не помните эти аргументы, Справка готова. После ввода формулы = СУММЕСЛИМН (Автозаполнение формул появится под формулой, а список аргументов будет указан в нужном порядке. Взгляните на изображение автозаполнения формул и списка аргументов в нашем примере сум_ранже - это D2: D11, столбец чисел, которые требуется суммировать. criteria_range1 - a2. A11 - столбец с данными, в котором находится условие1 "Южный". По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 - это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 - “Мясо”. Если щелкнуть СУММЕСЛИМН в автозавершении формулы, откроется статья, в которой вы сможете получить дополнительные сведения. Попробуйте попрактиковатьсяЕсли вы хотите поэкспериментировать с функцией СУММЕСЛИМН, вот несколько примеров данных и формула, использующая функцию. В этой Excel в Интернете книге вы можете работать с образцом данных и формулами прямо здесь. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты. Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
|
Читайте: |
---|
Популярное:
Новое
- Фотограф Всеволод Тарасевич: сумасшедшая жизнь от «Формирования интеллекта» и до «Края земли
- Требуется продавец-консультант?
- «Полная неожиданность»: в России рухнули продажи электроники
- На слонимщине перерисовали соломенные фигуры, так как они уж очень напоминали известных людей беларуси
- Трудовая мотивация и удовлетворенность трудом Похожие работы на - Профессиональное удовлетворение работой разными поколениями сотрудн
- Как получить грант на начало бизнеса, руководство от первого лица
- Разделение рабочего времени на части
- Презентация на английском языке И
- Как формировать профили должностей для поиска ценных сотрудников?
- Рабочее время в нестандартных ситуациях По пятницу с 9 00