Excel: как подсчитать уникальные значения на основе нескольких критериев
Вы можете использовать следующую базовую формулу в Excel для подсчета уникальных значений на основе нескольких критериев:
=SUM(--(LEN(UNIQUE(FILTER( A:A ,(Criteria1)\*(Criteria2)\*(Criteria3),"")))>0)) Эта конкретная формула подсчитывает количество уникальных значений в столбце A на основе выполнения трех критериев.
В следующем примере показано, как использовать эту формулу на практике.
Пример: подсчет уникальных значений на основе нескольких критериев в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает конференцию и очки, набранные различными баскетболистами:
Теперь предположим, что мы хотим подсчитать количество уникальных имен игроков, соответствующих следующим критериям:
- Игрок находится в западной конференции.
- У игрока более 20 очков.
Мы можем использовать следующую формулу для подсчета количества уникальных имен игроков, соответствующих этому критерию:
=SUM(--(LEN(UNIQUE(FILTER( A2:A14 ,( B2:B14 ="West")\*( C2:C14 >20),"")))>0)) Мы можем ввести эту формулу в ячейку E2 нашей электронной таблицы:
Из вывода мы видим, что есть 3 уникальных имени игрока, которые находятся в западной конференции и имеют более 20 очков.
Мы можем убедиться, что это правильно, вручную идентифицируя каждого игрока, который соответствует обоим критериям:
Из четырех игроков, соответствующих обоим критериям, есть три уникальных имени игрока:
Обратите внимание, что в этом примере мы выполнили уникальный подсчет, используя два критерия, но мы можем использовать аналогичный синтаксис, чтобы использовать столько критериев, сколько нам нужно.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Подсчет количества вхождений значения
Предположим, что нужно определить, сколько раз встречается конкретный текст или число в диапазоне ячеек. Например:
- Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.
- Если столбец содержит «Бьюкенен», «Додсворт», «Додсворт» и «Додсворт», «Dodsworth» возникает три раза.
Подсчитать количество вхождений значения можно несколькими способами.
Подсчет количества вхождений отдельного значения с помощью функции СЧЁТЕСЛИ
Используйте функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз встречается определенное значение в диапазоне ячеек.
Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.
Подсчет количества вхождений на основе нескольких критериев с помощью функции СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.
Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:
СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН.
Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
Предположим, что вам нужно определить, сколько продавцов продало определенный товар в определенном регионе, или вы хотите узнать, сколько продаж за определенное значение было выполнено определенным продавцом. Функции IF и COUNT можно использовать вместе; то есть сначала используется функция IF для проверки условия, а затем, только если результат функции IF имеет значение True, для подсчета ячеек используется функция СЧЁТ .
- Формулы, приведенные в этом примере, должны быть введены как формулы массива.
- Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
- Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версий и хотите изменить формулу или создать аналогичную формулу, нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала ожидаемые результаты. В более ранних версиях Excel для Mac используйте клавиши
Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.
Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Примечания: Формулы, приведенные в этом примере, должны быть введены как формулы массива.
- Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
- Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версий и хотите изменить формулу или создать аналогичную формулу, нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала ожидаемые результаты. В более ранних версиях Excel для Mac используйте клавиши
Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.
Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Приведенная выше функция говорит, что если D2:D7 имеет счета для Бьюкенен менее чем за 9000 долл. США, сумма должна отобразить сумму записей, в которой выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Подсчет частоты возникновения нескольких значений с помощью сводной таблицы
Сводную таблицу можно использовать для отображения итогов и подсчета вхождения уникальных значений. Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных. Вы можете использовать ее для развертывания и свертывания уровней представления данных, чтобы получить точные сведения о результатах и детализировать итоговые данные по интересующим вопросам. Кроме того, можно перемещать строки в столбцы или столбцы в строки («сводить» их) для просмотра количества вхождений значения в сводной таблице. Рассмотрим пример электронной таблицы «Продажи», в которой можно подсчитать количество значений продаж для разделов «Гольф» и «Теннис» за конкретные кварталы.
-
Введите данные в электронную таблицу Excel.
- Выделите диапазон A2:C8
- Выберите Вставка >Сводная таблица.
- В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК. Пустая сводная таблица будет создана на новом листе.
- В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
- Перетащите элемент Спорт в область Строки.
- Перетащите элемент Квартал в область Столбцы.
- Перетащите элемент Продажи в область Значения.
- Повторите третье действие. Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения». На этом этапе область «Поля сводной таблицы» будет выглядеть так:
- В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
- В диалоговом окне Параметры поля значений выполните указанные ниже действия.
- На вкладке Операция выберите пункт Количество.
- В поле Пользовательское имя измените имя на Количество.
Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Подсчитать количество отличников
Здравствуйте!
Сделала все, кроме последнего условия. Подскажите пожалуйста как такое реализовать.Есть таблица, она представлена на картинке.
Нужно посчитать количество отличников. Т.е. тех у кого нет троек и стоит зачет.
По предметно сосчиталось все просто, а вот как сосчитать без троек, никак не могу понять.Пыталась использовать СЧЕТЕСЛИ(), но ничего не получилось, т.к. он считал из всего диапазона.
В общем буду очень признательна за идеи.94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:Подсчитать количество отличников и хорошистов
C клaвиатуры вводятся средние баллы студентов за семестр Подсчитать количество отличников и.Используя фрагмент программы, решить задачу. Подсчитать количество отличников
Используя фрагмент программы, решить задачу. Подсчитать количество отличников. Examen = record.
Введя по две оценки для N-студентов подсчитать среди них количество отличников и неуспевающих
Введя по две оценки для N-студентов подсчитать среди них количество отличников и неуспевающих .Определить количество отличников 1-2 курса на всех факультетах группы у которых нет отличников
// Сторити масив структур, яки складаються з елементів: факультет, курс, група, призвіще.3897 / 2302 / 776
Регистрация: 02.11.2012
Сообщений: 6,126
файл приложите.
Регистрация: 15.02.2013
Сообщений: 121
как вы хотите определят отличников? по каждому предмету или по всем?
680 / 190 / 60
Регистрация: 04.05.2012
Сообщений: 879
Записей в блоге: 1
Сообщение от Vlad999 
файл приложите.
как вы хотите определят отличников? по каждому предмету или по всем?В том то и дело, что по предметно посчитать тут ерунда.
А мне нужно вычислить по всем,т.е. кто из трех студентов ни по одному предмету не получил тройки(((лаб в11.xls (30.5 Кб, 141 просмотров) 3897 / 2302 / 776
Регистрация: 02.11.2012
Сообщений: 6,126
первое что пришло в голову=СУММПРОИЗВ((C6:C8>3)*(D6:D8>3)*(B6:B8>3)*(E6:E8="з"))
680 / 190 / 60
Регистрация: 04.05.2012
Сообщений: 879
Записей в блоге: 1Сообщение от Vlad999 1
=СУММПРОИЗВ((C6:C8>3)*(D68>3)*(B6:B8>3)*(E6:E8=»з»))заработало))
только я не понимаю почему Вы имеено эту функцию использовали. не могли бы Вы прокомментировать свой выбор.3897 / 2302 / 776
Регистрация: 02.11.2012
Сообщений: 6,126как для меня она более понятна в применении и условий можно много вписать, не нужен трех палый ввод.
если у вас эксель 2007 то можно СЧЕТЕСЛИМН применить.87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
Помогаю со студенческими работами здесьПодсчитать количество отличников и студентов, которые учатся на «хорошо» (что не так с кодом?)
#include <iostream> #include <math.h> using namespace std; int main() < float a, j, h, z.Выбрать отличников и неуспевающих, подсчитать их колличество
по итогам месячной аттестации по предмету выбрать отличников и неуспевающих, подсчитать их.Определить неуспевающих студентов и их количество, отличников и их количество, и средний бал группы
Даны итоги успеваемости по предмету "информатика".Определить неуспевающих студентов и их.По итогам месячной аттестации по предмету выбрать отличников и неуспевающих, подсчитать их число
Помогите пожалуйста нужно написать программу на java.Или воспользуйтесь поиском по форуму:
Как посчитать количество отличников в excel
Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:
Задача — подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта.
Рассмотрим несколько способов ее решения.
Способ 1. Если нет пустых ячеек
Если вы уверены, что в исходном диапазоне данных нет пустых ячеек, то можно использовать короткую и элегантную формулу массива:
Не забудьте ввести ее как формулу массива, т.е. нажать после ввода формулы не Enter, а сочетание Ctrl+Shift+Enter.
Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF) . Если представить это в виде дополнительного столбца, то выглядело бы оно так:
Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:
Способ 2. Если есть пустые ячейки
Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):
Ссылки по теме
Доброго времени суток, могли бы вы, для новичка, разобрать данную формулу, начиная со знака крышки.
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
Спасибо, Ваш пример =СУММПРОИЗВ. работает даже при вводе вручную в пустую ячейку
Для полного диапазона:
Для диапазона с пустыми ячейками:
Неудобство в том, что диапазон должен обязательно содержать пустые значения, чтобы «-1» было в тему. Всё-таки метод от MCH наиболее универсален, не говоря уже о красоте. Всем доброго времени суток! Можно ли усовершенствовать формулу так, чтобы считать количество уникальных элементов в отфильтрованных строках (при использовании автофильтра)? Что-то вроде ПромежуточныхИтогов, где будут считаться количество уникальных. Не уверен, что это можно реализовать формулой. Я бы писал макрофункцию на VBA в таком случае.
Я сделал так.
1) В отдельной колонке вывел результат 1/Число вхождений, как на последней картинке в Способ 1
2) На эту колонку наложил функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; колонка )
В результате, когда применяю фильтр, получаю суммарное количество уникальных значений.Недостаток один — если фильтр наложить по другим полям, получим неверное отображение количества уникальных значений.
Огромное спасибо. Ваш способ именно то, что мне пригодилось!
Как посчитать уникальных значений из списка деталей по длине и ширине,когда длина и ширина в разных ячейках. Склеить длину и ширину в один столбец с помощью функции СЦЕПИТЬ (CONCATENATE) и дальше подсчитать уникальные, как описано в статье выше. А мне еще нужно посчитать кол-во уникальных значений не просто в одном столбце, а ещё по двум критериям в таблице. Это реально? Можно ли как-то соединить с СЧЁТЕСЛИМН? См. выше. Я бы склеил все критерии в отдельный столбец с помощью функции СЦЕПИТЬ и искал уникальные потом по этой колонке. Хотя не видя примера детально ответить сложно.
Буквально вчера бился с этой задачей, но вроде бы победил Допустим, что в указанном примере в столбце «B» стоит требуемый нам дополнительный параметр, например «цвет». Тогда формула будет выглядеть следующим образом:
У меня данная формула выдает неправильный результат.
Вот что нашел на соседнем сайте. Эта формула у меня работет.
Тот же вопрос, например мне нужно посчитать кол-во уникальных значений, при том в определенном диапазоне дат.
Например Таблица содержит наименования продуктов и даты их выпуска )) Подскажите.. )8)
Добрый день. А есть ли возможность вставить подсчет уникальных данных в сводную таблицу? Например в вычисляемое поле? Вычисляемые поля в сводных таблицах весьма ограничены по возможностям — формулу массива или сложную функцию туда точно не вставить. В новом Excel 2013 подсчет уникальных значений добавлен в стандартный набор вычислений по полю, кстати.
Здравствуйте! Просьба помочь с решением такой задачи: нужно по каждой дате подсчитать количество машин (по гос. номеру). №№ ТТН пренебречь.
Дат в 2014 году было много, машин — тоже.
Заранее спасибо!Гос.№ авто № ТТН Дата Т 240РВ 403826/12 01.03.2014 Т 240РВ 403826/12 01.03.2014 Т 240РВ 403826/12 01.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 С 215ВН 403825/14 04.03.2014 Т 118ЕЕ 14/2-2 06.03.2014 Т 118ЕЕ 14/2-2 06.03.2014 Андрей, если у вас Excel 2013, то можно использовать сводную таблицу — она в 2013 версии умеет подсчитывать количество уникальных элементов.
Если версия более древняя, то прогнать список сначала через удаление дубликатов ( Данные — Удалить дубликаты ) с галочками Гос.№ и Дата, а потом построить сводную (Даты закинуть в строки, а Гос№ в значения).Николай, спасибо за ответ!
Остался момент: в сводной таблице не получается найти кнопку «Уникальные значения». У меня 2013 эксель. Андрей, когда строите сводную, то обязательно установите флажок Добавить в модель данных .
Потом в сводной щелкаете правой по полю значений — Параметры поля — Операции — Число различных элементов.Флажок ставлю. А «Кол-ва уникальных» не вижу в настройках поля данных. Оно там на вкладке Операции — правильно называется Число различных элементов (Distinct Values)
Гос. № машины заменил на число — и заработало! а смешанный формат Гос.№ не дает появиться пункту «Число различных элементов». Есть рецепт как решить эту проблему? В смысле — без выборки 3 цифр гос. номера. В случае их уникальности.
Гос.№ авто Дата Кол-во дат Кол-во машин Т 240РВ 01.03.2014 1 Т 240РВ 01.03.2014 Т 240РВ 01.03.2014 Т 240РВ 01.03.2014 1 С 215ВН 04.03.2014 2 С 215ВН 04.03.2014 С 215ВН 04.03.2014 С 215ВН 04.03.2014 С 2155ВН 04.03.2014 С 215ВН 04.03.2014 С 215ВН 04.03.2014 С 215ВН 04.03.2014 С 215ВН 04.03.2014 С 215ВН 04.03.2014 1 Т 118ЕЕ 06.03.2014 3 Т 118ЕЕ 06.03.2014 Т 118ЕЕ 06.03.2014 О 754ТМ 06.03.2014 О 754ТМ 06.03.2014 О 754ТМ 06.03.2014 О 754ТМ 06.03.2014 1 В 093ЕЕ 07.03.2014 4 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 В 093ЕЕ 07.03.2014 1 Кол-во дат считает верно, а кол-во машин в день — неверно.
Андрей, я бы сделала так: делаем из этой таблицы сводную. В строки ставим поле » Дата » — получаем список всех возможных дат, копируем его на отдельный лист. Добавляем в сводной в строки поле » Гос.№ авто » , обязательно в «макете отчета» — «Повторять все подписи элементов» (работает с 2010-го). И с помощью СЧЁТЕСЛИ подтягиваем к списку дат количество машин. Это вариант без дополнительных условий. Если нужно учесть еще критерии (например, количество машин с отгрузкой не менее определенной суммы) — СЧЁТЕСЛИМН меня всегда выручает.
Подскажите пожалуйста, как решить задачку. У меня к сожалению не получается самостоятельно.
Есть несколько строк из 10 чисел.
Нужно посчитать сколько раз одновременно в строку входят 2 или 3 других числа.
Здравствуйте, подскажите, есть ли какие-либо ограничения по кол-ву строк или версии Офиса?
У меня на 35 тыс. строк (Офис 2013) не работает, выдает «0». При этом, ограничив в этом же массиве до 1500 строк, считает корректно
Ребят, а как просчитать значения которые повторяются, чтобы указывалось не общее количество, а порядочное, нп:
Имя Подсчет
Груша 1
Груша 2
Груша 3
Груша 4Вместо такого, что выдает формула «счетесли»
Имя Подсчет
Груша 4
Груша 4
Груша 4
Груша 4Нужно у аргумета «диапазон» функции СЧЁТЕСЛИ строку нижней границу указать без доллара. И будет тебе счастье…
Есть ещё один замечательный способ, как можно посчитать уникальные значения. С помощью функции ЧАСТОТА:
И что ещё интереснее, так можно считать уникальные значения при наличии условия. Например, если в столбце B были бы названия магазинов (где, например, есть в наличии эти фрукты), можно написать формулу, считающую количество уникальных фруктов в данном магазине:
- Функция ЧАСТОТА пропускает пустые ячейки и текст.
В продолжение темы, ещё один вариант подсчёта уникальных значений.
Сначала сортируем диапазон A1:A10 по возрастанию (или по убыванию, не важно). Потом вводим такую формулу массива:
В этом варианте преимущество в том, что расчёт происходит очень быстро, даже если в исходнике много тысяч строк.
С пропуском пустых ячеек и в немассивной форме:
=СУММПРОИЗВ((A2:A10<>A1:A9)*(A2:A10<>«»)
Всем привет! Подскажите как посчитать уникальные(повторяющиеся) значения в столбце таблицы, при использовании промежуточных итогов?
Округ отпр. Город отпр. Округ приб. Город приб. Число рейсов Центральный Москва Центральный Рязань 0 Центральный Москва Центральный Тверь 0 Центральный Москва Центральный Балашиха 1 Центральный Москва Южный Краснодар 3 Центральный Москва Уральский Тюмень 1 Центральный Рязань Сибирский Новосибирск 22 Сибирский Новосибирск Южный Краснодар 1 Уральский Тюмень Сибирский Новосибирск 12 Уральский Тюмень Южный Краснодар 2 Уральский Тюмень Южный Сочи 11 Сибирский Новосибирск Центральный Тверь 2 Южный Краснодар Центральный Рязань 11 Должно получиться что-то типа такого:
Округ Число узлов Центральный 5 Сибирский 1 Уральский 1 Южный 2 Какие-то города могут быть только в части «отправление», какие-то — в «прибытии», а какие-то в обоих частях, и у некоторых могут быть нулевое число рейсов (если у найденного города везде ноль в числе рейсов — то он не считается, а если хоть в одном месте не ноль — то считается).
Проблема в том, что надо искать каждое найденное в одном из двух столбцов по обоим столбцам. Если искать сначала в одном, затем во втором, затем сложить — по некоторым пунктам будет задвоение.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
- Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. — при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:
Стипендиальная ведомость факультета представляет собой ЭТ Excel , содержащую 5 рабочих листов. Соответственно Лист 1 – курс 1, Лист 2 – курс 2 и т. д.
На каждом рабочем листе составлены списки двух групп по 25 человек. Каждая таблица содержит следующие поля: №п\п, ФИО, оценки по пяти предметам, средний балл, успеваемость, стипендия, стипендия с надбавкой хорошистам и отличникам, стипендия с надбавкой из дополнительного фонда.
Поля: №п\п, ФИО, оценки по пяти предметам заполняются, остальные поля расчетные. (пример на рисунке)
Успеваемость
Успеваемость студентов определяется по следующей схеме: если средний балл 4,75 и выше, присваивается категория «отличник», если в промежутке от 3,75 до 4,75 – «хорошист», в промежутке от 2,5 до 3,75 – «троечник», если средний балл меньше 2,5 – «неуспевающий».
Для расчета успеваемости используется логическая функция ЕСЛИ. Данная функция включает в себя три Условия, три Истины и Ложь. Выглядит следующим образом:
Условие1 – средний балл >=4,75; ему соответствует Истина1 «отличник»;
Условие2 – средний балл >=3,75; ему соответствует Истина2 «хорошист»;
Условие3 – средний балл >=2,5; ему соответствует Истина3 «троечник»;
Ложью является значение «неуспевающий».
Стипендия
В условии задачи заявлено, что стипендия студентам, чей балл меньше 3,5 не начисляется. Стипендия остальным студентам составляет 460 руб.
Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – средний балл
Стипендия с надбавкой хорошистам и отличникам
Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.
Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:
Условие1 – категория «отличник»;
Условие2 – категория «хорошист»;
Истина – стипендия с надбавкой 10%;
Стипендия с доп.надбавкой
Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:
Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».
Рассчитать величину стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле Стипендия с надбавкой хор и отл, вставить функцию СУММ.
Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.
После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.
Рассчитать дополнительный фонд, умножив общий фонд на 50%
Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».
Т.к. у нас на рабочем листе две таблицы, для расчета общего количества отличников на курсе необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить вычисления по каждому курсу отдельно.
Таблица в режиме отображения формул выглядит следующим образом
Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.
Рассчитать величину надбавки каждому отличнику. Для этого необходимо дополнительный фонд разделить на количество отличников.
Рассчитать Стипендию с доп.надбавкой.
Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – категория успеваемости студента — «отличник»;
Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);
Ложь – стипендия без изменений.
Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».
Читайте также:
- Как переустановить оперу на планшете
- Яндекс браузер не хочет открывать хтмл ссылки в папках
- Как посмотреть комментарии в powerpoint на телефоне
- Как в powerpoint уменьшить таблицу в
- Минимальный размер дневного пособия в 1с зуп что это