Как рассчитать критерий ученика в Excel

Обновлено: 21.11.2024

При анализе оценок (особенно для большого класса) функция СЧЁТЕСЛИ в Excel может оказаться чрезвычайно полезной.

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

СЧЁТЕСЛИ подсчитает, сколько элементов (букв, цифр или слов) в выбранном вами диапазоне соответствует заданным вами критериям поиска. Скажем, например, вы хотите узнать, сколько учеников получили итоговую оценку F в вашем классе, как показано в примере ниже. Начните с ввода =СЧЁТЕСЛИ( в пустой ячейке, затем диапазона, который вы хотите найти (C2:C18 в приведенном ниже примере) и ячейки, содержащей критерии, которые вы хотите найти (D4 в приведенном ниже примере), и нажмите Введите. (Если вы предпочитаете, вместо того, чтобы вводить имена ячеек в формуле, щелкните левой кнопкой мыши в начале диапазона и, удерживая, перетащите до конца, введите запятую, щелкните ячейку с критериями, введите закройте скобки и нажмите Enter.)


После этого вы увидите общее количество критериев, которые вы искали. В этом примере мы видим цифру 3, указывающую на то, что трое учащихся получили оценку F.


Вы также можете вручную ввести диапазон непосредственно в формулу СЧЁТЕСЛИ на панели ввода текста ячейки, заключив его в кавычки. В этом примере, например, вы должны ввести формулу СЧЁТЕСЛИ(C2:C:18,"F").

Однако в этом случае вместо того, чтобы указать критерий «F» внутри формулы СЧЁТЕСЛИ, формула ссылалась на выделенную ячейку (D4). Когда вы используете СЧЁТЕСЛИ таким образом, вы можете легко изменить буквенную оценку, и счётчик будет автоматически обновляться без необходимости изменять формулу. Например, если теперь вы хотите узнать, сколько учеников получили пятерку, вам нужно всего лишь изменить буквенную оценку в выделенной ячейке и нажать Enter. Счетчик обновляется автоматически, как показано на снимке экрана ниже:


Хотя этот метод может показаться не таким полезным при работе с небольшими объемами данных, его использование с классами большего размера может значительно сэкономить время!

Формула для оценки – это вложенная формула ЕСЛИ, которая проверяет определенные критерии и возвращает конкретную оценку, если они соблюдены. Поиск оценки ученика, вероятно, является основным примером, который все руководства по Excel дают своим ученикам, чтобы объяснить логику логических функций в Excel. ЕСЛИ, И, ИЛИ, ЛОЖЬ, ИСТИНА и ЕСЛИОШИБКА — одни из самых популярных логических функций в Excel.

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

В этой статье объясняются различные способы определения оценок по баллам учащихся, уровня эффективности сотрудников, расчета премий и многих других вещей с помощью формулы оценки в Excel.

Как использовать формулу для расчета оценок в Excel?

Давайте разберемся, как использовать формулу для расчета оценок на нескольких примерах.

В каждом примере рассматривается отдельный вариант использования, реализованный с помощью формулы оценки в Excel.

Поиск оценок учащихся: вложенные условия ЕСЛИ

Поиск оценок учащегося — один из лучших примеров для понимания логичности условия ЕСЛИ в Excel. Обратите внимание на приведенную ниже оценку учащихся для этого примера.

Ниже приведены критерии определения оценки.

  • Если оценка выше 550, оценка A+.
  • Если оценка выше 500, оценка A.
  • Если оценка выше 450, оценка B+.
  • Если оценка выше 400, оценка B
  • Если оценка выше 300, оценка C
  • ЕСЛИ оценка не соответствует ни одному из вышеперечисленных условий, оценка будет НЕ ПРОЙДЕНА.

Чтобы найти индивидуальную оценку учащегося, нам нужно проверить все эти условия, и на основе выполнения условий; нам нужно объявить оценки.

Обучение Excel (21 курс, более 9 проектов) 21 онлайн-курс | 9 практических проектов | 110+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,9 (10 098 оценок)

Используя условие ЕСЛИ, мы можем найти оценки здесь. Прежде чем я расскажу вам, как найти оценки, позвольте мне сначала объяснить условие ЕСЛИ.

Логический тест: это аргумент для проверки логики значения.

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

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

Теперь взглянем на простой пример.

Если день равен ВОСКРЕСЕНЬЮ, то цена должна быть 35, иначе нам нужна цена 30.

Шаг 1. Откройте условие ЕСЛИ в ячейке B2.

Шаг 2. Теперь нам нужно выполнить логический тест, т. е. определить, совпадает ли день с ВОСКРЕСЕНЬЕМ или нет. Выберите ячейку A2 и проверьте, соответствует ли она значению ВОСКРЕСЕНЬЕ или нет.

Шаг 3. Если логический тест верен, результат равен 35.

Шаг 4. Если логический тест ложный, результат должен быть 30.

Шаг 5. Закройте скобку и нажмите клавишу Enter. У нас есть результат.

Шаг 6. Теперь мы перетащим эту формулу из ячейки B2 в ячейку B3. Таким образом, мы получим результат, показанный ниже.

Теперь давайте применим эту логику, чтобы найти оценки учащихся с помощью формулы для оценки в Excel.

Шаг 1. Откройте условие ЕСЛИ в ячейке C2. Первый логический тест — если оценка выше 550. Таким образом, проверьте, является ли оценка> 550 или нет, если это значение ИСТИННОГО идентификатора теста должно быть «A».

Шаг 2: Если тест неверен, значение должно быть. Здесь нам нужно проверить еще несколько критериев. Итак, откройте новое условие ЕСЛИ.

Шаг 3. Теперь проверьте второй критерий, т. е. значение > 500. Если этот логический тест верен, результат должен быть «B+».

Шаг 4. Теперь, если оба условия НЕВЕРНЫ, нам нужно проверить третий критерий. Итак, откройте еще одно условие ЕСЛИ и проверьте, равно ли значение> 400 или нет. Если тест ИСТИНЕН, результат должен быть «B».

Шаг 5. Таким же образом примените условия ЕСЛИ ко всем критериям и закройте скобки. Я уже применил все условия ЕСЛИ и получил результаты.

Шаг 6. Теперь мы перетащим эту формулу из ячейки C2 в ячейку C9. Таким образом, мы получим результат, показанный ниже.

Поиск категорий сотрудников: комбинация условий IF и AND

Мы увидели, как использовать условие ЕСЛИ для определения оценок учащихся. Теперь давайте посмотрим на комбинацию условий IF и AND.

У меня есть таблица данных о сотрудниках с указанием их отдела и года работы в компании.

Чтобы найти класс, необходимо использовать следующие критерии: "Если сотрудник работает в отделе продаж и его стаж работы >=5 лет, то сотрудник должен иметь право на продвижение по службе".

Кроме отдела продаж никто не имеет права на продвижение.

Здесь нам нужно проверить два условия одновременно, в отличие от предыдущего, где нам нужно проверить множество критериев по отдельности. Здесь нам может помочь комбинация условий IF и AND.

Функция И проверяет несколько условий одновременно, и если все переданные условия верны, мы получим результат ИСТИНА или ЛОЖЬ.

Шаг 1. Откройте условие ЕСЛИ в ячейке D2.

Шаг 2. Логический тест здесь не единственный; скорее, нам нужно проверить два условия для одного сотрудника. Итак, откройте функцию И внутри условия ЕСЛИ.

Шаг 3. Первый критерий – соответствует ли отдел отделу продаж или нет. И второй тест: составляет ли год службы >=5 лет или нет.

Шаг 4. Поскольку у нас есть только два условия для проверки, закройте квадратную скобку функции И. Если логическая проверка с помощью функции И имеет значение ИСТИНА, то результатом должно быть право на повышение, а если логическая проверка с помощью функции И имеет значение ЛОЖЬ, то результатом должно быть несоответствие требованиям к повышению. Закройте скобку и нажмите клавишу Enter, чтобы завершить формулу.

Шаг 5. Теперь мы перетащим эту формулу из ячейки D2 в ячейку D9. Таким образом, мы получим результат, показанный ниже.

Что нужно помнить

  • Если вы определяете оценку, проверяя одновременно несколько условий, и если все условия должны быть истинными, то для получения точных результатов необходимо использовать условия ЕСЛИ и И.
  • Если вы определяете оценку, проверяя несколько условий одновременно, и если хотя бы одно из условий верно, то для получения точных результатов необходимо использовать условия ЕСЛИ и ИЛИ. Условие ИЛИ возвращает значение ИСТИНА, если выполняется любое из указанных условий, и возвращает ЛОЖЬ, если ни одно из условий не выполняется.
  • Когда вы находите оценку на основе числовых значений, нам нужно сначала передать самое высокое значение. Он должен быть в порядке убывания.
  • Все текстовые значения должны быть заключены в двойные кавычки, а числовые значения не должны заключаться в двойные кавычки.

Рекомендуемые статьи

Это руководство по формуле оценки в Excel. Здесь мы обсудили, как использовать формулу для расчета оценок в Excel, а также практические примеры и загружаемый шаблон Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –

Функция СРЗНАЧЕСЛИ — это статистическая функция Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые крайне важно знать аналитику Excel, который вычисляет среднее значение заданного диапазона ячеек по определенному критерию. По сути, СРЗНАЧЕСЛИ вычисляет центральную тенденцию, которая является расположением центра группы чисел в статистическом распределении. Эта функция появилась в Excel 2007.

Формула

=СРЗНАЧЕСЛИ(диапазон, критерии, [средний_диапазон])

Функция СРЗНАЧЕСЛИ использует следующие аргументы:

  1. Диапазон (обязательный аргумент). Это диапазон одной или нескольких ячеек, который мы хотим усреднить. Аргумент может включать числа или имена, массивы или ссылки, содержащие числа.
  2. Критерии (обязательный аргумент). Критерии определяют, как будет усредняться ячейка. Критерии могут быть в форме выражения, числа, ссылки на ячейку или текста, определяющего, какие ячейки усредняются. Например, 12, " *(Выпечка)", B12:B15).

Пример. Как найти среднее значение с пустыми или непустыми критериями

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

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

Например, нам дана общая стоимость подготовки трех предметов. Используя формулу =СРЗНАЧЕСЛИ(B5:B7, «=», C5:C7), Excel вычислит среднее значение для ячейки B5:B7, только если ячейка в столбце A в той же строке пуста, как показано ниже:

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

Например: =СРЗНАЧЕСЛИ(B5:B7, "", C5:C7)

Теперь предположим, что мы хотим найти среднее значение только тех значений, которые соответствуют пустым ячейкам. В этом сценарии мы будем использовать «<>» в критериях.

Теперь, используя те же данные, используйте следующую формулу:

Результатом будет среднее значение параметра "Выпечка и темный шоколад", равное 150.

Что нужно помнить о функции СРЗНАЧЕСЛИ

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

  • Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
  • Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
  • Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Предположим, у вас есть данные об именах учащихся, их разделах и итоговых оценках. Два студента могут иметь одинаковое имя в разных разделах. Вас просят найти оценку учащегося по его имени и разделу.

Пример файла

1. СУММПРОИЗВ


2. СУММА (ФОРМУЛА МАССИВА)



ФОРМУЛА :

Нажмите CTRL+SHIFT+ENTER, чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически расставит фигурные скобки <. >вокруг формулы.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

Эта формула не работает в Excel 2003 и более ранних версиях.

=INDEX(D3:D10,MATCH(1,(B3:B10=D12)*(C3:C10=D13),0))

Нажмите CTRL+SHIFT+ENTER, чтобы подтвердить эту формулу . Если все сделано правильно, Excel автоматически расставит фигурные скобки <. >вокруг формулы.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:


5. ИНДЕКС-ПОИСКПОЗ (Версия 2)

=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,ЕСЛИ(B3:B10=D12,ЕСЛИ(C3:C10=D13,1)),0))

Нажмите CTRL+SHIFT+ENTER для подтверждения этой формулы. Если все сделано правильно, Excel автоматически расставит фигурные скобки <. >вокруг формулы.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:


ФОРМУЛА :

=ПРОСМОТР(2,1/(B3:B10=D12)/(C3:C10=D13),(D3:D10))

7. ЕСЛИ СООТВЕТСТВИЕ НЕ НАЙДЕНО



ФОРМУЛА :

=ЕСЛИОШИБКА(ИНДЕКС(D3:D10,СООТВЕТСТВИЕ(1,(B3:B10=D12)*(C3:C10=D13),0)),"Нет совпадения")

Это формула возвращает «Нет соответствия», если значение не существует на основе условий.
Нажмите CTRL+SHIFT+ENTER, чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически расставит фигурные скобки <. >вокруг формулы.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

Об авторе:

Deepanshu основала ListenData с простой целью: сделать аналитику простой для понимания и использования. Он имеет более чем 10-летний опыт работы в области науки о данных. За время своего пребывания в должности он работал с глобальными клиентами в различных областях, таких как банковское дело, страхование, частный капитал, телекоммуникации и управление персоналом.

Хотя мне нравится иметь друзей, которые согласны, я учусь только у тех, кто не
Отправляйтесь по электронной почте LinkedIn

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