Функции МАКС и МИН в Excel по условию
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
=МАКС(ЕСЛИ(A2:A13=D2;B2:B13))
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе.
ЕСЛИ(A2:A13=D2;B2:B13)
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Поделиться в социальных сетях:
Какие существуют обозначения ошибок и способы их исправления? — MS Excel
Если формула содержит ошибку, то «Excel 2007» отобразит специальное сообщение об ошибке. При этом каждый тип ошибки обозначается своим сообщением, вызывается разными причинами и соответственно требует различных способов разрешения.
##### — Что обозначает и как исправить?Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.
В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).
Во втором же случае надо:
- проверить формулу, если вычисляется число дней между двумя датами;
- если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.
#ЗНАЧ! — Что обозначает и как исправить?Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ!.
#ДЕЛ/0! — Что обозначает и как исправить?Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.
- В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
- Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
- Нажмите Enter или кнопку «Ввод» на строке формул.
[stextbox id=»warning»]Если в качестве операнда используется пустая ячейка, то он автоматически считается равным нулю.[/stextbox]
#ИМЯ? — Что обозначает и как исправить?Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.
1 вариант
Если используется имя, которое не было определено, то необходимо сделать следующее:
- В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
- В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.
Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».
2 вариант
Если существует ошибка в написании имени, то необходимо проверить его орфографию.
- В окне открытого листа нажмите клавишу F3.
- В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
- Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
- Для закрепления нажмите клавишу Enter.
3 вариант
Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10).
- В окне открытого листа выделите ячейку с ошибкой в написании функции.
- Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
- В списке команд выберите пункт «Изменить в строке формул».
- На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
- Закрепите результат щелчком по клавише Enter.
4 вариант
Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
СУММ(A1 А10) вместо СУММ(A1:А10).5 вариант
Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10).
6 вариант
Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).
#Н/Д — Что обозначает и как исправить?Эти символы сообщают, что нужное значение не доступно для функции или формулы.
1 вариант
Если в формулу были введены недостающие данные, а также #Н/Д или НД(), то #Н/Д необходимо заменить новыми данными.
[stextbox id=»warning»]Обозначение #Н/Д вводится в ячейки, данные из которых еще не доступны.[/stextbox]
2 вариант
Если в функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.
3 вариант
Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.
4 вариант
Если в формуле используется недоступная в данный момент функция, то необходимо проверить, что книга, использующая функцию листа, открыта, а также то, что функция правильно работает.
5 вариант
Если для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ, для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ.
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.
6 вариант
Если в формуле массива используется аргумент, не соответствующий диапазону, указанному в формуле массива, то необходимо проверить диапазон ссылок формулы на соответствие числу строк и столбцов или ввести формулу массива в меньшее число ячеек.
7 вариант
Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.
#ССЫЛКА! — Что обозначает и как исправить?Эти символы сообщают о неверной ссылке на ячейку.
1 вариант
Если ячейка, на которую ссылается формула, была удалена или же в данную ячейку помещено значение скопированных ячеек, то необходимо изменить формулу с учетом новых ссылок.
2 вариант
Если используется функция OLE, связанная с незапущенной программой, то необходимо запустить требуемую программу.
[stextbox id=»info»]Интерфейс OLE (Object Linking and Embedding — связывание и внедрение объектов) поддерживается множеством различных программ и используется для помещения документа, созданного в одной программе, в другую программу. Например, можно вставить документ Word в книгу Excel и наоборот.[/stextbox]
3 вариант
Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.
4 вариант
Если используется макрос, вызывающий макрофункцию, которая при определенных вариантах выдает значение #ССЫЛКА!.
Необходимо проверить аргумент функции и удостовериться, что он ссылается на допустимые ячейки или диапазоны ячеек.
#ЧИСЛО! — Что обозначает и как исправить?Это сообщение об использовании неправильных числовых значений в формуле или функции.
1 вариант
Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.
2 вариант
Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.
3 вариант
Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.
#ПУСТО! — Что обозначает и как исправить?Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.
1 вариант
Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:
- для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например,
СУММ(С1:С20). - для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например,
СУММ(С1:С20;D1:D20).
2 вариант
Если указанные диапазоны не имеют общих ячеек, то необходимо изменить ссылки, добиваясь нужного пересечения.
#ЗНАЧ! Ошибка в Excel | Как это исправить? (Шаг за шагом)
Ошибка является распространенной и неотъемлемой частью формул и функций Excel, но исправление этих ошибок делает вас профессионалом в Excel. Как новичок, найти эти ошибки и восстановить их для правильной работы непросто. Каждая ошибка возникает только из-за ошибок пользователя. Итак, важно знать свои ошибки и почему эти ошибки происходят.
В этой статье мы покажем вам, почему мы получаем #ЦЕННОСТЬ! Ошибки в Excel и как их исправить.
Например, простая формула может возвращать значение #ЗНАЧ! Ошибка, если символ пробела в любой ячейке создается для очистки ячейки. Чтобы исправить ошибку, необходимо выделить ячейку, содержащую пробел, и нажать клавишу Delete.
Содержание
- Что такое #Value! Ошибка в Экселе?
- Как исправить #Value! Ошибка в Экселе?
- Дело №1
- Дело №2
- Пример
- Дело №3
- Пример
- Здесь следует помнить
- Рекомендуемые статьи
- Как исправить #Value! Ошибка в Экселе?
Как исправить #Value! Ошибка в Экселе?
Вы можете скачать этот шаблон Excel со значением ошибки здесь — Шаблон Excel со значением ошибки
Дело № 1
#ЗНАЧ!: Эта ошибка ExcelОшибка ExcelОшибки в Excel распространены и часто возникают во время применения формул. Список из девяти наиболее распространенных ошибок Excel: #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.
read. больше происходит по нескольким причинам в зависимости от формулы, которую мы используем. Наиболее распространенной причиной этой ошибки является неправильный тип данных, используемый в ссылках на ячейки.
Выполните следующие действия, чтобы исправить ошибку значения в Excel.
- Посмотрите на приведенную ниже формулу для добавления различных значений ячеек.
В приведенной выше базовой формуле Excel мы пытаемся сложить числа от A2 до A6 в ячейке A7, и мы получили результат #ЗНАЧ! Ошибка . В ячейке A5 у нас есть значение «Сорок», , что является неправильным типом данных, поэтому возвращается #ЗНАЧ! - Чтобы получить правильную сумму этих чисел, мы можем использовать функцию СУММ в Excel.
- Получаем следующий результат.
Функция СУММ проигнорировала неверный тип данных в ячейке A5, добавив оставшиеся значения ячеек и выдав итог. - В противном случае мы можем изменить текстовое значение в ячейке A5, чтобы получить правильный результат.
Для предыдущей формулы мы только изменили значение ячейки A5 на 4000. Итак, теперь наша предыдущая функция работает правильно.
Случай #2
Теперь мы увидим второй случай #ЗНАЧ! Ошибка в формулах Excel.
Пример
- Посмотрите на приведенную ниже формулу.
Мы разделили столбец B на столбец A. И получили три разных результата.
- Результат 1 говорит B2/A2. В обеих ячейках у нас есть числовые значения, и результат равен 80%.
- Результат 2 говорит B3/A3. Поскольку в ячейке B3 нет значения, мы получили результат 0%.
- Результат 3 говорит B4/A4. Это тот же случай, что и Результат 2.
- У нас есть #ЗНАЧ! Ошибка , такой любопытный случай.
- Основная причина этой ошибки заключается в том, что пустая ячейка не является действительно пустой, так как может быть ошибочного пробела.
В ячейке B4 у нас есть символ пробела, который не виден невооруженным глазом. Тем не менее, это причина, по которой у нас есть #ЗНАЧ! Ошибка .
Мы можем использовать функцию LEN ExcelФункция LENExcelФункция Len возвращает длину заданной строки. Он вычисляет количество символов в заданной строке в качестве входных данных. Это текстовая функция в Excel, а также встроенная функция, доступ к которой можно получить, набрав =LEN( и введя строку в качестве ввода. Подробнее или функцию ISBLANK Excel для обработки этих незаметных пробелов.
- Функция LEN даст количество символов в выбранной ячейке. LEN рассматривает пробел как один символ.
Посмотрите на приведенную выше функцию. У нас есть один символ, приводящий к ячейке B4, что подтверждает, что ячейка B4 не является пустой ячейкой.
- Аналогично, функция ЕПУСТО показывает ИСТИНА , если ячейка пуста. В противном случае отображается FALSE .
Посмотрите на результат выше. В результате мы получили FALSE для ячейки B4. Итак, мы можем сделать вывод, что ячейка B4 не является пустой ячейкой.
Случай #3
Еще один случай с результатом #VALUE! Ошибка Excel связана с тем, что тип данных аргумента функции хранится неправильно.
Пример
Посмотрите на изображение ниже.
- В приведенном выше примере формулы мы использовали функцию ЧИСТРАБДНИ ExcelФункция ЧИСТРАБДНИ ExcelФункция ЧИСТРАБДНИ — это функция даты и времени, которая определяет количество рабочих дней между двумя заданными датами и широко используется в области финансов и бухгалтерского учета. При расчете рабочих дней ЧИСТРАБДНИ автоматически исключают выходные (субботу и воскресенье). Узнайте больше, чтобы найти фактические рабочие дни между двумя датами.
- Первые две ячейки получили результат, но у нас есть результат ошибки #ЗНАЧ!
Дата окончания в ячейке B4 имеет значение NA, значение без даты, в результате чего получается #VALUE!
- Нам нужно ввести правильное значение даты в ячейку B3, чтобы исправить эту ошибку.
Существует еще один шанс получить ту же ошибку, даже если у нас есть значение даты.
- Например, посмотрите на ячейку ниже.
Несмотря на то, что у нас есть дата в ячейке B4, у нас все еще есть #ЗНАЧ! Ошибка. В ячейке C4 дата не является датой, хранящейся в виде текстового значения, поэтому нам нужно преобразовать ее в правильный формат даты, чтобы получить правильный результат.
О чем следует помнить
- Другие значения ошибок описаны в отдельных статьях. Эта статья посвящена #ЦЕННОСТЬ! Ошибка.
- #ЦЕННОСТЬ! Ошибка возникает по нескольким причинам. Мы перечислили выше все возможные сценарии этой ошибки.
Рекомендуемые статьи
Эта статья была руководством по Excel #ЗНАЧ! Ошибка. Здесь мы обсуждаем исправление ошибки значения в Excel с примерами и загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей: –
- Ошибки ВПР
- Панели ошибок Excel
- При ошибке Перейти к 0 в VBA
- При ошибке Возобновить Далее в VBA
Как использовать меньше или равно Функция в Excel | Малый бизнес
Стивен Мелендес Обновлено 9 апреля 2019 г.
Microsoft Office имеет ряд операций сравнения, поэтому вы можете проверить, является ли значение больше, равно или меньше другого значения, используя стандартные символы больше, меньше и равно. Вы также можете комбинировать их, чтобы проверить, является ли одно значение меньшим или равным или большим или равным другому значению. Эти операции особенно полезны в формулах, в которых используется функция ЕСЛИ или аналогичные функции, например функция СУММЕСЛИ в Excel.
Операции сравнения Excel
Если вы создаете формулу Excel, вы можете использовать различные операции сравнения для сравнения значений, включая записи в ячейках или буквенные значения, такие как «5» или «ABC».
Например, чтобы определить, больше ли значение в ячейке B2 пяти, можно использовать формулу =B2>5. Все формулы в Excel начинаются со знака равенства, поэтому первый символ вводит формулу, ничего не говоря о равенстве. Чтобы увидеть, меньше или равно пяти, вы должны использовать формулу =B2<=5.
Как правило, вы можете использовать знаки «больше», «меньше» и «равно», которые вы помните из уроков математики. Если вы хотите проверить, не равны ли два значения, используйте специальную нотацию Excel не равно, которая состоит из символов меньше и больше, например =B2<>5. Эти операции возвращают значение TRUE, если оно истинно, и FALSE, если оно ложно.
Часто вам потребуется использовать эти функции в формулах, которые включают логические операции, такие как ЕСЛИ. Функция ЕСЛИ проверяет, является ли ее первый аргумент истинным или ложным, и возвращает второй аргумент, если он истинен, и третий, если ложно. Например, =IF(B2<=5, C2, D2) заполнит ячейку значением в ячейке B3, если значение в B2 меньше или равно пяти, и в противном случае заполнит ее значением в ячейке D2.
Если вам нужно использовать функцию ЕСЛИ Excel и несколько условий, рассмотрите возможность использования вместо нее функции ЕСЛИ. Это позволяет тестировать несколько условий по порядку, выбирая первое, которое возвращает true.
Аргументы представляют собой список условий, за каждым из которых следует значение, которое следует принять, если это условие истинно.
Например, формула =ЕСЛИ(A1 < 5, 1, A1 < 10, 2, A1 < 15, 3, A1>=15, 4) заполнит ячейку «1», если значение в A1 меньше чем пять, «2», если оно меньше 10, «3», если оно меньше 15, и «4» в противном случае. Если ни одно из условий не является ИСТИННЫМ, функция возвращает ошибку «#Н/Д». Чтобы этого не произошло, вы можете добавить окончательное условие по умолчанию, которое представляет собой просто слово «ИСТИНА», которое по определению всегда будет соответствовать, если больше ничего не соответствует.
Функция СУММЕСЛИ в Excel
В Excel есть и более сложные функции, которые могут суммировать или подсчитывать значения при соблюдении определенных условий.
СУММЕСЛИ принимает в качестве аргументов диапазон ячеек для оценки в соответствии с критериями, критерии их оценки и, необязательно, диапазон ячеек для суммирования.