Как посчитать количество отличников в excel

Подскажите пожалуйста, как решить задачку. У меня к сожалению не получается самостоятельно.
Есть несколько строк из 10 чисел.
Нужно посчитать сколько раз одновременно в строку входят 2 или 3 других числа.

Ребят, а как просчитать значения которые повторяются, чтобы указывалось не общее количество, а порядочное, нп:

Имя Подсчет
Груша 1
Груша 2
Груша 3
Груша 4

Вместо такого, что выдает формула "счетесли"

Имя Подсчет
Груша 4
Груша 4
Груша 4
Груша 4

Есть ещё один замечательный способ, как можно посчитать уникальные значения. С помощью функции ЧАСТОТА:

И что ещё интереснее, так можно считать уникальные значения при наличии условия. Например, если в столбце B были бы названия магазинов (где, например, есть в наличии эти фрукты), можно написать формулу, считающую количество уникальных фруктов в данном магазине:

• Функция ЧАСТОТА пропускает пустые ячейки и текст.
  

В продолжение темы, ещё один вариант подсчёта уникальных значений.

Сначала сортируем диапазон A1:A10 по возрастанию (или по убыванию, не важно). Потом вводим такую формулу массива:

В этом варианте преимущество в том, что расчёт происходит очень быстро, даже если в исходнике много тысяч строк.

Проблема в том, что надо искать каждое найденное в одном из двух столбцов по обоим столбцам. Если искать сначала в одном, затем во втором, затем сложить - по некоторым пунктам будет задвоение.

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
• Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не 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 руб.

Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:

Условие – средний балл <3,5;

Стипендия с надбавкой хорошистам и отличникам

Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.

Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:

Условие1 – категория «отличник»;

Условие2 – категория «хорошист»;

Истина – стипендия с надбавкой 10%;

Стипендия с доп.надбавкой

Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:

Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».

Рассчитать величину стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле Стипендия с надбавкой хор и отл, вставить функцию СУММ.

Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.

После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.

Рассчитать дополнительный фонд, умножив общий фонд на 50%

Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».

Т.к. у нас на рабочем листе две таблицы, для расчета общего количества отличников на курсе необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить вычисления по каждому курсу отдельно.

Таблица в режиме отображения формул выглядит следующим образом

Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.

Рассчитать величину надбавки каждому отличнику. Для этого необходимо дополнительный фонд разделить на количество отличников.

Рассчитать Стипендию с доп.надбавкой.

Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:

Условие – категория успеваемости студента - «отличник»;

Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);

Ложь – стипендия без изменений.

Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».

Читайте также:

  
• Как переустановить оперу на планшете
  
• Яндекс браузер не хочет открывать хтмл ссылки в папках
  
• Как посмотреть комментарии в powerpoint на телефоне
  
• Как в powerpoint уменьшить таблицу в
  
• Минимальный размер дневного пособия в 1с зуп что это