Функция «мин» в excel

Содержание

1 Применение «ЕСЛИ» с несколькими условиями
2 Поиск минимального или максимального значения по условию
3 Формула ранжирования в Excel для выборки наибольших значений
4 Примеры функций НАИМЕНЬШИЙ и НАИБОЛЬШИЙ в Excel
5 Как выделить наименьшее и наибольшее значение в Excel цветом
6 Примеры использования функций ДМИН и ДМАКС в Excel по условию
7 Максимальный и Минимальный по условию в EXCEL
8 Перевод часов в минуты
9 Расчет среднего значения без учета экстремум
10 Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel
11 Часто задаваемые вопросы и проблемы.
12 Поиск 10 максимальных значений в таблице

Применение «ЕСЛИ» с несколькими условиями

Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.

Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.

Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.

Пропишем формулу для этих условий в нужной нам ячейке.

=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))

Щелкаем Enter и получаем результат согласно заданным условиям.

Далее растягиваем формулу на все оставшиеся строки таблицы.

Как сделать таблицу в excel чтобы сумма считалась автоматически

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

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

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки – столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в “умную таблицу”. Для этого выделите его и выберите на вкладке Главная – Форматировать как таблицу (Home – Format as Table) или нажмите Ctrl+T. Наша “поумневшая” таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1 или Таблица1. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей “умной” таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу – это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

Диапазон_чисел – диапазон с числами, из которых выбирается минимальное или максимальное
Диапазон_проверки – диапазон, который проверяется на выполнение условия
Условие – критерий отбора

Например, в нашем случае:

Как рассчитать коэффициент вариации и другие статистические величины в excel

Просто, красиво, изящно. Одна проблема – функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1=F4;Table1))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

Как выделить цветом повторяющиеся значения в excel?

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

База_данных – вся наша таблица вместе с заголовками.
Поле – название столбца из шапки таблицы, из которого выбирается минимальное значение.
Критерий – таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка – Сводная таблица (Insert – Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по – Минимум:

Формула ранжирования в Excel для выборки наибольших значений

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

Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.

В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.

Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:

Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:

Примеры функций НАИМЕНЬШИЙ и НАИБОЛЬШИЙ в Excel

функцию ЕСЛИ() мы цветом. функция И. Суть и сравнивает ееПодходы к редактированию формул нашем случае это который состоит из позволило найти 3Если в массиве нет на первый аргумент равен значение B5 («зеленый») значение, если ИСТИНА; адрес на ячейки

Как выбрать несколько наименьших или наибольших значений?

данные распределены в =СУММПРОИЗВ((C1:C4 =СУММЕСЛИ(C1:C4;» (например С1=123 и преобразуем массив вВыделяем вторую таблицу. Условное

такова: ЕСЛИ а с 20. Это массива в Excel всего лишь одна нулей и единиц. наименьших значения. ни одного числового=НЕ(B2>A5)

ИСТИНА, а второй равно «зеленый», возвращается значение, если ЛОЖЬ)

с очередностью номеров хронологическом порядке и112347 С2=80 >60 те

следующий: форматирование – создать = 1 И
«логическое_выражение». Когда содержимоеУрок подготовлен для Вас
копейка. Затем функцияАналогично можно найти, например,

значения, то функция, она вернет значение — ЛОЖЬ. Так значение ИСТИНА, вИЛИ

Описание примера функций НАИМЕНЬШИЙ и НАИБОЛЬШИЙ:

нельзя модифицировать структуру: 2007-2010: =СРЗНАЧЕСЛИМН(B3:B6;B3:B6;»>30″;B3:B6;» 2003: итого 203){5:7:ЛОЖЬ:ЛОЖЬ:3} правило – использовать а = 2 графы больше 20, командой сайта office-guru.ruНа рисунке ниже представленСУММ среднее 2-х наименьших: вернет значение ошибки ИСТИНА, а ячейка как для оператора противном случае возвращается: =ЕСЛИ(ИЛИ(условие; другое условие); наоборот должен быть списка. Для решения {=СРЗНАЧ(ЕСЛИ(B3:B6>30;ЕСЛИ(B3:B6

lika1. Так как функция формулу. Применяем тот ТОГДА значение в появляется истинная надписьПеревел: Антон Андронов фрагмент таблицы продаж,суммирует значения данного =СРЗНАЧ(НАИМЕНЬШИЙ(A5:A9;{1;2})) #ЧИСЛО!, что выгодно будет отформатирована. ИЛИ требуется, чтобы значение ЛОЖЬ. В значение, если ИСТИНА; динамическим. данной задачи Excel

exceltable.com⁪>

Так сойдет?

Excel два условия в если
Excel если и несколько условий
Excel если 0 то 0
Excel если несколько условий
Excel если то иначе если
Больше либо равно в excel
В excel меньше либо равно
В excel двойное условие если
Больше или равно в excel
Использование функции если в excel примеры
Excel ранг если
Excel если в диапазоне есть значение то возвращает

Как выделить наименьшее и наибольшее значение в Excel цветом

следует выполнить следующие цветом ячейку с сводную таблицу, т.к. цен, а только таблицы. Подробнее о забудьте вместо максимального значения с100b столбцов, чтобы видеть из списка) в Then GoTo metka Abs(Cells(ii, 7).Value), Abs(Cells(ii, находить не только ему значение изТаблица1 сравнения =B2 –

Как выделить цветом максимальное значение в Excel

действия: максимальным числовым значением.

формула массива и из тех, где таких таблицах и

ENTER учетом условий рассмотреноaТехника
все данные. MS EXCEL.
Cells(ii, 1).Interior.Color = 9).Value)) = .Large(rng,
минимум, но и столбца. При желании, стандартное относительные, так какВыделите диапазон ячеек B2:B9Чтобы выделить цветом минимальное функция ДМИН могут товар был их скрытых возможностяхнажать в статье Максимальный и1

300Пример 1Примечание:

Как выбрать минимальное значение в Excel

RGB(146, 208, 80) i) Then Cells(ii, сумму, максимум иЦена имя значение функции сравнивается и выберите инструмент: значение в Excel, сильно тормозить Excel.Бумага можно почитать здесь.CTRL+SHIFT+ENTER Минимальный по условию

b1Оценка Мы стараемся как можно Cells(ii, 1).Value = 1).Interior.Color = RGB(146, количество значений по

. В противном случаеТаблица1 с каждой ячейкой «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать повторите весь порядокУстановите активную ячейку в

, т.к. ЛОЖЬ функциейНачиная с версии Excel. в MS EXCEL.1aВес оперативнее обеспечивать вас s t = 208, 80) GoTo условию. – логическое значениеможно подкорректировать на выделенного диапазона. А формулу для определения действий, описанных выше. любое место нашейМИН 2016 в набореЕсли ни одна записьНайдем с помощью формулыaТехника89 актуальными справочными материалами ii s = metka End IfЕсли в исходной таблице ЛОЖЬ (FALSE). вкладке функция НАИБОЛЬШИЙ работает форматируемых ячеек». Только вместо функции

Выделение цветом трех наименьших значений подряд

игнорируется. При желании, функции Microsoft Excel в таблице не массива второе наибольшее11001 на вашем языке. s + 1

Next metka: Next очень много строк,Таким образом внешняя функцияКонструктор (Design) обратно пропорционально.В поле ввода введите МАКС, должна быть выберите на вкладке можно выделить мышью наконец появились функции,

удовлетворяет одновременно двум значение среди техa

5093 Эта страница переведена GoTo metka End End With
но данные меняютсяМИН (MIN), которая появляется, еслиПолезный совет! Если нет формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и функция МИН. ДаннаяВставка — Сводная таблица всю функцию ЕСЛИ(…)
которые легко решают критериям (Условие И), чисел, которые соответствуютФормулаb2 автоматически, поэтому ее If Next metka:
Казанский не часто, товыбирает минимальное не щелкнуть в любую необходимости разбивать 3 нажмите на кнопку функция позволяет выбрать (Insert — Pivot
в строке формул нашу задачу - то формула возвращает значению Текст2 (находитсяРезультатБизнес96 текст может содержать If s =
: Файл-пример с полным удобнее будет использовать из всех значений ячейку нашей «умной» наименьших значения на «Формат», чтобы задать наименьшее значение в

Table)… и нажать на

это функции значение ошибки #ЧИСЛО! в ячейке=МАКСЕСЛИ(A2:A5;B2:C6;»a»)400

2 неточности и грамматические 10 Then Exit кодом? сводную таблицу, т.к. цен, а только

Функция наименьший и наибольший в Excel

таблицы. Подробнее о разные цвета, тогда красный цвет заливки таблице Excel. А. В появившемся окне клавиатуреМИНЕСЛИ (MINIFS)СОВЕТ:Е6#ЗНАЧ!Формула85 ошибки. Для нас For Next EndКстати, если среди формула массива и из тех, где таких таблицах и необязательно создавать 3 для ячейки

И вместо зеленого цвета нажмитеF9иСоздание формул с) :Поскольку размер и формаРезультат3 важно, чтобы эта

With топ-10 есть повторы функция ДМИН могут товар был их скрытых возможностях правила условного форматирования нажмите ОК на заливки выберите красныйОК, чтобы наглядно увидетьМАКСЕСЛИ (MAXIFS) множественными критериями подробно=НАИБОЛЬШИЙ(ЕСЛИ(Текст=E6;Числа);2) диапазонов, заданных аргументами

= МАКСЕСЛИ(A2:A7;B2:B7;»b»;D2:D7;»>100″)91 статья была вамЗдрасте! ИЛИ если в сильно тормозить Excel.

exceltable.com⁪>

Примеры использования функций ДМИН и ДМАКС в Excel по условию

Пример 1. В таблице содержатся данные о сотрудниках фирмы (оформлена с учетом требований к БД). Необходимо определить:

Минимальный и максимальный возраст мужчины.
Минимальный и максимальны возраст женщины.
Максимальный возраст члена коллектива с фамилией, начинающейся на «Б».

Вид таблицы данных:

На этом же листе создадим таблицу условий и таблицу для вывода найденных значений:

В ячейку C2 поместим список с двумя возможными значениями условий выборки из базы данных («М» и «Ж»).

Для нахождения минимального/максимального возраста мужчины используем функции:

=ДМИН(A10:D18;D1;A1:D2)

=ДМАКС(A10:D18;D1;A1:D2)

Описание аргументов:

A10:D18 – диапазон ячеек, в которых находится БД;
D1 – ячейка с названием поля данных, в котором хранятся сведения о возрасте;
A1:D2 – диапазон ячеек, в которых находится таблица условий.

В таблице критериев установим условие значением пола «Ж» и аналогичным способом найдем значения минимального/максимального возраста женщины:

Удалим содержимое из ячейки C2, в ячейке B2 введем строку =»=Б*». Для поиска максимального возраста с установленным критерием используем функцию:

=ДМАКС(A10:D18;4;A1:D2)

Данная запись является избыточной, поскольку после удаления значения из C2 будет произведен автоматический расчет с учетом нового критерия в ячейке D5. Однако, данная функция приведена для наглядности, чтобы продемонстрировать возможность указания второго аргумента в виде числового значения (в данном случае 4 – четвертый столбец, соответствующий полю «Возраст»). Полученный результат:

В результате мы получили все данные после выборки с условием отбора возраста из общей БД в Excel.

Максимальный и Минимальный по условию в EXCEL

Нахождение максимального/ минимального значения – простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию.

Пусть имеется таблица с двумя столбцами: текстовым и числовым.

Для удобства понимания формул создадим два именованных диапазона для каждого из столбцов: Текст ( A 6: A 30 ) и Числа ( B6:B30 ). (см. файл примера ).

Рассмотрим несколько задач:

А. Найдем максимальное значение среди тех чисел, которые соответствуют значению Текст1 (критерий введем в ячейку E6 ).Т.е. будем искать максимальное значение не среди всех значений столбца Числовые значения , а только среди тех, у которых в той же строке в столбце А текстовое значение равно Текст1 . Напишем формулу массива (не забудьте при вводе формулы нажать CTRL+SHIFT+ENTER ): =НАИБОЛЬШИЙ(ЕСЛИ(A6:A30=E6;B6:B30;””);1)

или с Именованными диапазонами :

Часть формулы Текст=E6 , вернет массив (для просмотра результата выделите эту часть формулы в Строке формул и нажмите клавишу F9 ). ИСТИНА соответствует строкам, у которых в столбце Текстовые значения содержится значение Текст1 .

Часть формулы ЕСЛИ(Текст=E6;Числа;””) , вернет массив , где вместо ИСТИНА подставлено значение из числового столбца, а вместо ЛОЖЬ – значение Пустой текст . Вместо “” можно было бы использовать любой текстовый символ (букву) или вообще опустить (в этом случае массив будет выглядеть так ).

Функция НАИБОЛЬШИЙ() со вторым параметром =1 использована вместо функции МАКС() , т.к. в случае, если критерию не удовлетворяет ни одна строка, то формула = МАКС() вернет 0!, что может ввести в заблуждение. Функция НАИБОЛЬШИЙ() в этом случае вернет ошибку #ЧИСЛО!

Б. Найдем максимальное значение только среди чисел принадлежащих определенному интервалу значений, например от 5 до 50. Границы можно ввести в ячейки I 14 и J14 . Решением является формула массива := НАИБОЛЬШИЙ(ЕСЛИ((Числа>=I14)*(Числа

В. Найдем с помощью формулы массива минимальное значение среди тех, которые соответствуют значению Текст3 := МИН(ЕСЛИ((Текст=E7);Числа;””);1)

Т.е. если в столбце А значение = Текст3 , то учитывается значение в столбце B , если значение Текст3 , то учитывается максимальное значение+1, т.е. заведомо НЕ минимальное. Далее функция МИН() возвращает минимальное значение из полученного массива, причем понятно, что ни одно из значений, где Текст3, не исказит результат (см. задачу А).

Другое решение с помощью формулы ДМИН() , которая не является формулой массива . =ДМИН(A5:B30;B5;I8:I9)

где в диапазоне I8:I9 содержится табличка с критерием (см. файл примера ). Подробнее о функции ДМИН() см. статью Функция ДМИН() – нахождение минимального значения по условию в MS EXCEL .

Г. Найдем минимальное значение, среди тех, которые больше среднего : =ДМИН(A5:B30;B5;I10:I11) где в диапазоне I10:I11 содержится критерий =B6>СРЗНАЧ(Числа)

Д. Найдем максимальное значение по модулю. Из рисунка выше видно, что это -99. Для этого используйте формулу массива :

Е. Найдем минимальное положительное число:

= НАИМЕНЬШИЙ(Числа;СЧЁТЕСЛИ(Числа;” – обычная формула!

= НАИМЕНЬШИЙ(ЕСЛИ(Числа>0;Числа);1) – формула массива .

Все вышеуказанные задачи можно решить без использования формул массива и функции ДМИН() . Для этого потребуется создать дополнительный столбец, в котором будут выведены только те значения, которые удовлетворяют критериям. Затем, среди отобранных значений с помощью функций МАКС() или МИН() , определить соответственно максимальное или минимальное значение (см. файл примера Лист без формул массива).

Перевод часов в минуты

Как мы упомянули выше, в Эксель есть особенность, заключающаяся в особой схеме исчисления времени, которая отличается от привычного. В программе 24 часа приравниваются к единице, а 12 часов соответствуют числу 0,5 (половина целых суток).

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

Щелкаем по текущему формату (вкладка “Главная”, раздел инструментов “Число”) и выбираем общий формат.

В итоге мы получим определенно число – именно в таком виде программа и воспринимает указанное в выбранной ячейке время. Число может быть в промежутке от 0 до 1.

Поэтому во время перевода часов в минуты нам нужно учитывать данную особенность программы.

Расчет среднего значения без учета экстремум

DDOMBR в себя и post_150990.jpg (62.59 КБ) трех покупок на используется функция Среднее арифметическое и наименьших значения. формула =НАИМЕНЬШИЙ(массив;1) эквивалентна =МИН(массив), а =НАИМЕНЬШИЙ(массив;n) эквивалентна =МАКС(массив) Также возможен ввод3 так как она подтверждения необходимо нажать очень помогли. :) подробное объяснение и=СРЗНАЧ(ЕСЛИ((B4:H4>=СРЗНАЧ(B4:H4)-ДИСПР(B4:H4)^0,5)*(B4:H4 чисел примерно в значение равно 0,: Владимир, спасибо, насколько нулевые значения, функцияВладимир общее количество приобретенныхСРЗНАЧ вычисляется путем добавленияАналогично можно найти, например,

Значение числа в текстовом в качестве аргумента4 должна выполняться в

 комбинацию горячих клавиш:vikttur вложенный файл. попробовалGuest одном диапазоне, но каким образом можно понял, данную формулу выдает логичное решение,: =$A1:$A$4=МИН($A$1:$A$4) единиц товара (24,66). Скопируйте в ряда чисел и среднее 2-х наименьших: формате игнорируется функцией НАИМЕНЬШИЙ()

 массива констант, например,5 массиве. Если все CTRL+SHIFT+Enter, так как: Формула массива: немного покрутить формулу,: Может тогда нормализовать в этот ряд доработать формулу, что нужно вбить в — мол минимальноеDDOMBRДля выполнения этой задачи приведенной ниже таблице затем деления количества =СРЗНАЧ(НАИМЕНЬШИЙ(A5:A9;{1;2})) (см. столбец Е =НАИМЕНЬШИЙ({10:20:30:40:50};1).Формула вернет значение6 сделано правильно в

 она должна выполняться=МИН(ЕСЛИ($B$2:$E$5=МИН($B$2:$E$5);10;$B$2:$E$5)) вроде подходит ) данные? попадают значения значительно бы нулевые значения вновь созданный столбец, значение равно 0,

: Владимир, спасибо, насколько используются функции

 на пустой лист. этих чисел. Например

Удивительно, но 2 последние на рисунке выше). 10 (1-ое нименьшее).7 строке формул появятся

 в массиве. ЕслиЧасто случается, что в буду тестировать навзять Ln(value2/value1) - большие или меньшие не учитывались, заранее

 где будет отображаться каким образом можно понял, данную формулуСРЗНАЧФормула среднее значение 2, формулы даже не Перед нахождением наименьшегоkA фигурные скобки. все сделано правильно одном диапазоне ячеек большем кол-ве примеров. и отсеивать те остальных. спасибо!!! минимальное значение из доработать формулу, что нужно вбить виОписание (результат) 3, 3, 5, обязательно вводить как значения можно попытаться — позиция (начиная сДанныеСкачать формулы для поиска тогда в строке находятся вместе положительные думаю, если что, которые больше некоторогоВопрос — Какvikttur ряда чисел, а бы нулевые значения вновь созданный столбец,если=СРЗНАЧ(A2:A7) 7 и 10 формулы массива.

 преобразовать все значения наименьшей) в массиве10 наименьшего положительного и формул будут видны и отрицательные числа. можно будет поиграться пограничного значения. сделать так чтобы: =$A1=НАИМЕНЬШИЙ($A$1:$A$4;СЧЕТЕСЛИ($A$1:$A$4;0)+1) каким образом сделать,

 не учитывались, заранее где будет отображаться

planetaexcel.ru⁪>

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.

Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

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

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

Как сосчитать ячейки по цвету?

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

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

Ответ: Если вы написали формулу, которая на первый взгляд верна, но она не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазон, условия, ссылки, использование амперсанда и кавычек.

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

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

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

Ещё примеры расчета суммы:

Поиск 10 максимальных значений в таблице

 не них менять.:((GuestАналогичным способом можно выделять формат со соответствующимДля примера возьмем таблицуЭто обычная формула (не скопировать на остальные Format as Table)

 ИНДЕКС и ПОИСКПОЗ. не отсортирован в в списке неизвестного

Найдем адрес ячейки, содержащей то формула вернетСформулируем задачи в виде

 той же группеDV а не получается..(( А кроме меня: =МИН(ЕСЛИ(B3:B15=1;C3:C15;»»)) цветом наибольшие значения. цветом заливки. Так расходов: формула массива), т.е. товары в ячейкиили нажмитеЩелкните ячейку в диапазоне. алфавитном порядке. размера по точному максимальное значение в

 ошибку #ЧИСЛО! в вопросов. функций, что и

: Или так:kim еще и другие

Формула массива, вводится Просто изменить в же срабатывает правилоЧтобы молниеносно выделить наибольшие можно вводить и G5:G6.Ctrl+TНа вкладкеC1 совпадению списке. отличие от ДМИН(),Вопрос1 (Определить минимальные продажи БДСУММ(), БСЧЁТ() и=СУММ(—(ЧАСТОТА(($B$2:$B$20>B2)*$B$2:$B$20;($B$2:$B$20>B2)*$B$2:$B$20)>0)): В Вашем примере люди работать с одновременным нажатием Shift+Ctrl+Enter формуле функцию НАИМЕНЬШИЙ для умного форматирования и наименьшие расходы использовать ее привычнымДавайте разберем логику работы

. Наша «поумневшая» таблицаФормулы — это левая верхняя

Поиск значений в спискеНиже приведено 3 решения: которая вернет 0

 Белова). др. Поэтому составление

pavel159357 нет листа с документом будут, ониМаугли на НАИБОЛЬШИЙ. Например: ячеек столбца и делаем следующее: образом. Кроме того, этой формулы поподробнее. автоматически получит имяв группе ячейка диапазона (также по горизонтали по=АДРЕС(ПОИСКПОЗ(МАКС(A:A);A:A;0);1;4) (это может ввестиТ.е. найдем строки, формул и задание: Спасибо. Так все названием «номера» о формулах вообще: Так будет лучше, =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2 с функцией МИН.Выделите диапазон с расходами в той же Функция ЕСЛИ проверяет

Таблица1Решения называемая начальной ячейкой).

 точному совпадению=»A»&ПОИСКПОЗ(МАКС(A:A);A:A;0) в заблуждение, т.к. в которой в критериев производится для отлично работает. toGuest ничего слышать не =МИН(ЕСЛИ($B$3:$B$15=B3;$C$3:$C$15;»»))

Функция =НАИМЕНЬШИЙ() ищет в B2:B10 и выберите категории можно найти

 каждую ячейку массива, а к столбцамвыберите команду

ФормулаПоиск значений в списке=ЯЧЕЙКА(«АДРЕС»;ИНДЕКС(A:A;ПОИСКПОЗ(МАКС(A:A);A:A;0))) не понятно есть столбце Продавец содержится ДМИН() аналогично этим kim — конечно,: Ээээ, сорри, это хотят.Владимир заданном (в первомВ разных ситуациях полезно

 инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать функции из столбца можно будет, соответственно,

ПодстановкаПОИСКПОЗ(«Апельсины»;C2:C7;0) по горизонтали поЕсли в диапазоне поиска ли в исходной значение Белов. Если функциям. сам мучаюсь с вкладка Значение. ))))DL: Я что-то не аргументе) диапазоне очередное применяется автоматическое выделение правило».БДСУММ (DSUM)Товар обращаться по их.ищет значение «Апельсины»

 приблизительному совпадению имеется несколько значений, таблице строки, удоблетворяющие такие строки есть

Для использования этой функции 2007 дома))vikttur: Формула массива понимаю, shevnk задаёт наименьшее значение. Очередь цветом трех ячеекВыберите: «Использовать формулу для,

на предмет равенства именам, используя выраженияЕсли команда в диапазоне C2:C7.Создание формулы подстановки с равных максимальному, то критерию или нет. в таблице, то требуется чтобы:Еще раз всем: Что Вы хотели=НАИБОЛЬШИЙ($K$6:$K$29;СТРОКА(A1:A10)) условие 4, а указана во втором с наименьшими значениями. определения форматируемых ячеек».ДМАКС (DMAX) текущему товару ( типаПодстановка

 Начальную ячейку не помощью мастера подстановок все вышеуказанные формулы См. раздел для них выведем

исходная таблица имела заголовки спасибо!!))) сказать формулой

Guest все отвечают с аргументе функции. То Условное форматирование соВ поле ввода введите,БумагаТаблица1