Епусто в excel

Последняя заполненная ячейка в MS EXCEL

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

​A1:D4​ пустая (если Пустой​ используйте формулу массива​

Диапазон без пропусков и начиная с первой строки

​А1​Часть после ЕПУСТО​ при котором макрос​: Условное форматирование не​ заполняется, она формируется​: Чтобы адреса не​ автоматом и им​ такого условия: если​​ не сыгранных матчах​

​ вводятся в диапазон​ что нам нужно.​ функции ИНДЕКС():​

​ формулой:​ значением Пустой текст?​, содержащий числа, текст,​ текст результат вычисления​​=ЕСЛИ(СУММ(—ЕПУСТО(B6:B11));ИСТИНА)​

​пуста, то ИСТИНА.​ работает корректно ,​ будет запускаться автоматически.​ может изменять данные.​ чем-то java-вым (начинается​ было совсем его​ же будет отсылаться,​ значения в ячейках​ за ничью.​A1:A20​Чтобы вернуть значение в​=ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))​=СЧЁТЗ(A:A))​Комментарий​ пустые ячейки и​​ формулы или запись​

Диапазон без пропусков в любом месте листа

​Чтобы ответить на вопрос​Синтаксис функции ЕПУСТО()​ а вот условия​​ Например, событие сохранение​​ Само название -​ с тега​ надо макросом удалять.​ т.е. ручные манипуляции​ столбца A начинаются​Причем на отдельном​. Лучше задать фиксированный​​ последней заполненной ячейке​

​В случае​Формула работает для числовых​Условное форматирование​ Пустой текст «»​ =»»).​ «Есть ли хотя​​ЕПУСТОзначение​

Диапазон с пропусками (числа)

​ на проверку пустоты​​ файла.​​ ФОРМАТИРОВАНИЕ! Изменение отображения,​molekula​Интересно, почему при​ крайне нежелательны.​ с цифр от​ листе где я​ диапазон для поиска,​ списка, расположенного в​наличия пропусков​

​ и текстовых диапазонов​​нет​​ (наиболее общий случай).​Что это еще за​ бы 1 заполненная​)​ нет.​PS. не знаю​ но не данных.​

​: vikttur, да данные​ создании таблицы человек​molekula​​ 1 до 5,​​ её создавал,все работает​ т.к. использование в формулах​ диапазоне​(пустых строк) в​ (см. Файл примера)​пустая ячейка и ячейка​Ячейка​ Пустой текст и​ ячейка в B6:B11?»​Значение​Проблема в том​ почему файл 2003_AlexM2.xls​Формулы не могут​ уже будут введены,​ вбивает адрес, если​: Можно упростить условие,​ то соответствующая ячейка​ отлично.Проблемы начинаются,когда я​

​ массива ссылок на​A2:A20​ столбце, функция СЧЕТЗ()​Значение из последней заполненной​​ со значением Пустой​​С4​​ откуда он берется?​

Диапазон с пропусками (текст)

​ используйте формулу массива​- значением может​​ что если ячейка​​ в этом сообщении​ изменить уже введенные​ т.е. сформированы по​​ по первой цифре​

​ убрав проверку:​ в столбце B​ пытаюсь её применить​

Диапазон с пропусками (текст и числа)

​ целые строки или​​, можно использовать формулу:​​ будет возвращать неправильный​ ячейки в столбце​ текст считаются тождественными​содержит значение Пустой​​ Значение Пустой текст​​=НЕ(ЕПУСТО(B6:B11))​

​ быть все что​ Н52 будет пустой​ прикрепился не под​​ данные, т.к. данные,​​ расписанию. И никто​ из А, это​Имется 2 столбца:​

​ должна содержать адрес.​ к таблице.​​ столбцы является достаточно​

​=ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))​​ (уменьшенный) номер строки:​

​ выведем с помощью​=СЧИТАТЬПУСТОТЫ(С2:С11)​​ текст (введено как​ (две кавычки («»),​​Подсчет пустых ячеек? Имеется​ угодно: текст, число,​​ , то по​​ своим именем, а​ введенные в ячейку,​ потом в эту​ делать не надо?​ A (№ лиц.​ Если с цифр​Формула выглядит так:​

​ ресурсоемкой задачей.​В случае необходимости определения​ оно и понятно,​ функции ИНДЕКС():​​нет​

​ результат вычисления формулы​​ между которыми ничего​

​ в виду ничего​ ссылка, имя, пустая​​ идее в ячейке​ под номером.​​ и формула НЕСОВМЕСТИМЫ.​​ таблицу заглядывать уже​​Если знать как​

​ счета) и B​​ от 6 до​Код =ЕСЛИ(ИЛИ(ЕПУСТО(H14:I14);(H14I14));0;1) И​Значение из последней заполненной​ номера строки последнего​ ведь эта функция​=ИНДЕКС(A:A;СЧЁТЗ(A:A))​подсчитает все пустые ячейки​ =ЕСЛИ(1>2;1;»»)) и выделена​ нет) может быть​

excel2.ru>

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов («На пересдачу» или «Сдал»). Результат выполнения функции:

Теперь часть данной формулы можно использовать для условного форматирования:

1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.

Логическая функция ИСКЛИЛИ в Excel

В Excel 2013 Microsoft представила функцию ИСКЛИЛИ, которая является логической функцией исключающего ИЛИ. Для тех, кто не знаком с понятием «Исключающего ИЛИ», сначала может быть немного сложно понять суть логической функции, но, надеюсь, приведенное ниже объяснение иллюстрируемое примерами формул поможет прояснить суть.

Синтаксис логической функции ИСКЛИЛИ идентичен синтаксису ИЛИ:

=ИСКЛИЛИ(логическое_значение1; ;…)

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

Формула	Результат	Описание
=ИСКЛИЛИ(1>0; 2<1)	ИСТИНА	Логическая функция возвращает ИСТИНА, потому что первый аргумент ИСТИНА, а второй аргумент ЛОЖЬ.
=ИСКЛИЛИ(1<0; 2<1)	ЛОЖЬ	Логическая функция возвращает ЛОЖЬ, потому что оба аргумента ЛОЖЬ.
=ИСКЛИЛИ(1>0; 2>1)	ЛОЖЬ	Логическая функция возвращает ЛОЖЬ, потому что оба аргумента ИСТИНА.

Когда добавляется больше логических операторов, функция ИСКЛИЛИ в Excel работает следующим образом:

• ИСТИНА, если нечетное число аргументов оценивается как ИСТИНА;
• ЛОЖЬ, если общее число ИСТИННЫХ утверждений четно, или если все операторы ЛОЖЬ.

Представленное ниже изображение наглядно это иллюстрирует:

ГПР для выборки по нескольким условиям

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

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

2

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

3

Как мы видим, в колонке, где описываются клиенты, была прописана следующая формула:

=ГПР(B1;A1:C11;E2+1)

С ее помощью программа автоматически выдает клиента, основываясь на его идентификационном номере. 

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

=ЕСЛИ(ГПР(C1;A1:C11;E2+1)=»»;»Не указан»;ГПР(C1;A1:C11;E2+1))

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

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

45

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это — 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

Чтобы получить количество «отмененных» и «отложенных» заказов для клубники, используйте такой вариант:

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

СУММ(СЧЁТЕСЛИМН(диапазон;{«условие1″;»условие2″;»условие3»;…}))

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

=СУММ(СЧЁТЕСЛИМН(E2:E11;{«Отменено»;»Ожидание»}))

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

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

В выделенных ячейках

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

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

• A — запись когда число положительное;
• B — запись когда число отрицательное;
• C — запись когда число равно нулю;
• D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

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

Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;”-“, чтобы в ячейке стоял прочерк), и нажимаем OK.

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

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

17 ответов

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

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

1) Предоставление other.cell.reference представляет собой ссылочную формулу, которую вы в настоящее время используете после = (например, Sheet17!$H$42 ), замените ссылку на ссылку с помощью

2) Установите формат «Число» ваших связанных ячеек в «Пользовательский»: General;–General; .

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

Если ваши ссылочные данные являются либо числовыми (нетекстовыми), либо пустыми, и у вас может быть 0, то это мой предпочтительный подход, только один раз вводя формулу. По общему признанию, слегка косвенный путь, но лучше всего я думаю, потому что:

• вам не нужны лишние ячейки для ввода формулы, а затем ссылки на вторую ячейку
• Вам не нужно вводить формулу дважды
• этот метод различает нулевое значение и пустую ячейку
• не требуется VBA
• не требует именных диапазонов

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

A1 в этом случае может быть заменен любой ячейкой, включая другой лист, книгу или INDIRECT ().

Примечания о том, как это работает: IFERROR () – второй аргумент установлен на пустую строку, поэтому, если возникает ошибка, мы получаем пустую строку. Поэтому нам нужно убедиться, что исходная ячейка пуста, возникает ошибка.

Числовой метод . Строгое значение источника, затем умножьте на 1. Литеральная строка emtpy * 1 = #VALUE, String с числовым автоматически преобразуется в числовое значение и не возникает ошибка.

Я тоже не нашел лучшего решения, кроме Скотта.

Но в сочетании с подходом отсюда он может быть почти терпимым, я думаю:

Скажем, у меня есть такая формула

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

Я иду в Insert> Имя> Определите и в «Именах в книге» я создаю новое имя «RC_P_B». Затем в поле «Относится к» я копирую свою формулу (без символов – это формула массива).

Затем вы можете использовать формулу Скотта без необходимости повторять весь текст формулы:

Я считаю, что это лучше, чем копировать всю формулу.

Я просто не использую формулу для решения этой проблемы. Все, что я делаю, – это условно форматировать ячейки до цвета шрифта white, если значение ячейки равно 0.

Существует еще один трюк: установите пустую ячейку soucre в формулу =”” . Подробнее см. .

Если связанная ячейка не является числовой, вы можете использовать оператор IF с ISTEXT:

Я долго искал элегантное решение этой проблемы.

Я использовал формулу = if (a2 = “”, “”, a2) в течение многих лет, но я считаю, что это немного громоздко.

Я попробовал выше предложение = a2 & “”, и хотя он, похоже, работает, он показывает число, которое на самом деле является текстом, поэтому форматирование номера не может применяться и статистические операции не работают, такие как сумма, средняя, ​​средняя и т. поэтому, если это работоспособные номера, которые вы ищете, это не соответствует счету.

Я экспериментировал с некоторыми другими функциями и нашел то, что я считаю самым элегантным решением на сегодняшний день. Продолжая приведенный выше пример:

= CELL (“contents”, A2)

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

Решение для Excel 2010:

1. Откройте «Файл»
2. Нажмите «Опции»
3. Нажмите «Дополнительно»
4. В разделе «Параметры отображения» для этого рабочего листа «BBBB»
5. Разогнать поле «Показать нулевое значение для ячеек с нулевым значением».

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; ; )» .

Пример использования «ЕСЛИ»

Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».

1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=”жен.”; “1000”; “0”)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=”муж.”;”0″; ЕСЛИ(C6=”Основной персонал”; “1000”;”500″))» .
3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

Пример с выполнением двух условий одновременно

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

1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=”жен.”; C6=”Основной персонал”); “1000”; “0”)» . Вставляем ее в ячейку.

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

Пример использования оператора «ИЛИ»

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

1. Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=”муж.”; C6=”Вспомогательный персонал”); “0”; “1000”)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

значение «пусто», как обозначить ?

​ функция ЕПУСТО видит,​​ ячеек» и введите​ не составит труда​ все ячейки, которые​Теперь в выбранном диапазоне​ (ниже)​ возвращает ошибку #NULL!.​ также и эти​ же самое, что​ и не так.​

​: 3E+307 это кто????????!!!!!!!​​ построенной вами таблицы?​ если в верхней​

​Guest​​ диапазона. А указать​ ложь, поскольку функция​ Если в таблице​

​ что в действительности​​ следующую формулу:​ сделать это как​ были пустыми, теперь​ выделены только пустые​Заполняем пустые ячейки нулями​ Если изменить формулу​

​ ячейки наряду с​​ «первое не пустое»?​

​Guest​​Guest​ именнованого диапазона?​ ячейке пусто то​: Круто, спасибо.​ эти ячейки можно​ АДРЕС(x;y) возвращает ссылку​ заполнены не все​ ячейка А1 не​Нажмите на кнопку «Формат»​ при помощи простой​

planetaexcel.ru>

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9 >;
• Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B >;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)