Выбрать между данными в excel - Word и Excel - помощь в работе с программами

9 способов сравнить две таблицы в Excel и найти разницу

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

Когда у вас есть две похожие книги Эксель или, лучше сказать, две версии одной и той же книги, что вы обычно хотите с ними делать в первую очередь? Сравнить их на предмет различий, а затем, возможно, объединить в один файл. Кроме того, такая операция может помочь вам обнаружить потенциальные проблемы, такие как битые ссылки, повторяющиеся записи, несогласованные формулы.

Визуальное сравнение таблиц.
Быстрое выделение различий.
Использование формулы сравнения.
Как вывести различия на отдельном листе.
Как можно использовать функцию ВПР.
Выделение различий условным форматированием.
Сопоставление при помощи сводной таблицы.
Сравнение таблиц при помощи Pover Query.
Инструмент сравнения таблиц Ultimate Suite.

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

Просмотр рядом, чтобы сравнить таблицы.

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

Сравните 2 книги.

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

Чтобы просмотреть два файла Эксель рядом, сделайте следующее:

Откройте оба файла.
Перейдите на вкладку «Вид» и нажмите кнопку «Рядом». (1) Это оно!

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

Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):

В результате два отдельных окна будут расположены, как на скриншоте.

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

Расположите рядом несколько таблиц Excel.

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

Появится диалоговое окно «Сравнить рядом», в котором вы выберете файлы, которые будут отображаться вместе с активной книгой.

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

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

Сравните два листа в одной книге.

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

Откройте файл, перейдите на вкладку «Вид» и нажмите кнопку «Новое окно».

Это действие откроет тот же файл в дополнительном окне.
Включите режим просмотра «Рядом», нажав соответствующую кнопку на ленте.
Выберите лист 1 в первом окне и лист 2 во втором окне.

Быстрое выделение значений, которые различаются.

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии или же отсутствии отличий между записями, вам нужно на вкладке «Главная», выбрать кнопку «Найти и выделить», предварительно выделив диапазон, где надо сравнить данные в Эксель.

В открывшемся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

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

Формула сравнения.

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

Простейший вариант – сопоставление двух таблиц, находящихся на одном листе. Можно соотносить как числовые, так и текстовые значения, всего-навсего прописав в одной из соседних ячеек формулу их равенства. В результате при тождестве ячеек мы получим сообщение ИСТИНА, в противном случае — ЛОЖЬ.

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

=G3=C3

Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).

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

=G3=Лист2!C3

Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?

Можно подсчитать общее количество расхождений и сразу вывести это число где-нибудь отдельно.

=СУММПРОИЗВ(—(C3:C25<>G3:G25))

или можно сделать это формулой массива

{=СУММ(—(C3:C25<>G3:G25))}

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

Как произвести сравнение на отдельном листе.

Чтобы сравнить два листа Эксель на предмет различий, просто откройте новый пустой лист, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз и вправо, перетащив маркер заполнения:

=ЕСЛИ(Лист1!A1 <> Лист2!A1; «Лист1:»&Лист1!A1&» — Лист2:»&Лист2!A1; «»)

Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от расположения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 в Лист1 и Лист2, формула в B1 будет сравнивать ячейку B1 на обоих листах и так далее. Результат будет выглядеть примерно так:

В результате вы получите отчет о различиях на новом листе. Думаю, это достаточно информативно.

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

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

Как сравнить две таблицы при помощи формулы ВПР.

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

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

Для наглядности расположим обе таблицы на одном листе.

Формула

=ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «».

=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)=G3;»»;ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0))

Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —

=ЕСЛИ(J3<>»»;F3;»»)

а в K3 – его новая цена:

=ЕСЛИ(J3<>»»;G3;»»)

Ну а далее в L3 просто найдем разность K3-J3.

Таким образом, в отчёте сравнения мы видим только несовпадения значений второй таблицы по сравнению с первой.

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

В ячейке B2 запишем формулу

=ЕСЛИ(ЕНД(ВПР(A2;Прайс1!$B$3:$B$19;1;0));»Нет»;ВПР(A2;Прайс1!$B$3:$C$19;2;0))

Так мы выясним, какие цены из второй таблицы встречаются в первой.

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными —

=ЕСЛИ(ЕНД(ВПР(A2;Прайс2!$B$3:$B$22;1;0));»Нет»;ВПР(A2;Прайс2!$B$3:$C$22;2;0))

Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.

Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.

Выделение различий между таблицами цветом.

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

На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
На вкладке Главная кликните Условное форматирование > Новое правило и создайте его со следующей формулой:

=A1<>Лист2!A1

Где Лист2 — это имя другого листа, который вы сравниваете с текущим.

В результате ячейки с разными значениями будут выделены выбранным вами цветом:

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

Сравнение при помощи сводной таблицы.

Хороший вариант сравнения — объединить таблицы в единую сводную, и там уже сопоставлять данные между собой.

Вернемся к нашему примеру с двумя прайс-листами. Объединим наши данные на одном листе. Чтобы отличить данные одной таблицы от другой, добавим вспомогательный столбец D и укажем в нем, откуда именно взяты данные:

А теперь приступим к созданию сводной таблицы. Я не буду подробно останавливаться на том, как мы это будем делать. Все шаги подробно описаны в статье Как сделать сводную таблицу в Excel.

Поместим поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений.

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

Сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайсов и сортирует их по алфавиту. Причём, без повторов. У новых товаров нет старой цены, у удаленных товаров — новой цены. Легко увидеть изменения цен, если таковые были.

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

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

Плюсы: такой подход на порядок быстрее работает с большими объемами данных, чем ВПР. Можно сравнить данные нескольких таблиц.

Минусы: надо вручную копировать данные в одну большую таблицу и добавлять столбец с названием исходного файла.

Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в него данные практически из любых источников и преобразовывать потом их желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные, а для более ранних версий ее нужно отдельно скачать с сайта Microsoft и установить.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку Главная — Форматировать как таблицу. Имена созданных таблиц можно изменить на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые генерируются по умолчанию).

Загрузите первый прайс в Power Query с помощью кнопки Из таблицы/диапазона на вкладке Данные.

После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в…

В появившемся затем окне выбираем «Только создать подключение».

Повторите те же действия с новым прайс-листом.

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

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части определим способ объединения — Полное внешнее.

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

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

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

Теперь осталось вернуться на вкладку Главная и нажать Закрыть и загрузить.

Получаем новый лист в нашей рабочей книге:

Примечание. Если в будущем в наших прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все на вкладке Данные.

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

Это, пожалуй, самый красивый и удобный способ из всех стандартных. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров.

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

Они находят различия только в значениях, но не могут сравнивать формулы или форматирование ячеек.
Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку / столбец на одном листе, все последующие строки / столбцы будут отмечены как отличия.
Они хорошо работают на уровне листа, но не могут обнаруживать структурные различия на уровне книги Excel, к примеру добавление и удаление листов.

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

Как сравнить таблицы при помощи Ultimat Suite для Excel

Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, самым интересным из которых является «Сравнение таблиц» — инструмент для сравнения листов или диапазонов данных в Excel.

Чтобы сделать сравнение более интуитивным и удобным, надстройка разработана следующим образом:

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

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

Нажмите кнопку «Сравнить листы (Compare Two Sheets)» на вкладке «Данные Ablebits » в группе « Объединить »:

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

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

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

Совет. Если вы не уверены, какой алгоритм подходит вам, выберите вариант по умолчанию (без ключевых столбцов). Какой бы алгоритм вы ни выбрали, надстройка найдет все различия, только выделит их по-разному (целые строки или отдельные ячейки).

На этом же шаге вы можете выбрать предпочтительный тип соответствия:

Первое совпадение (по умолчанию) — сравнивает строку на листе 1 с первой найденной строкой на листе 2, которая имеет хотя бы одну совпадающую ячейку.
Наилучшее совпадение — сравнивает строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
Полное совпадение — находит на обоих листах строки, которые имеют одинаковые значения во всех ячейках, и отмечает все остальные строки как уникальные.

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

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

Скрытые строки и столбцы не имеют значения, и мы говорим надстройке игнорировать их:

Нажмите кнопку «Сравнить (Compare)» и подождите немного, пока программа обработает ваши данные и создаст их резервные копии. Резервные копии всегда создаются автоматически, поэтому вы можете не беспокоиться о сохранности своих данных.

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

На скриншоте выше различия выделены цветами по умолчанию:

Красные строки — строки, существующие только на Листе 2 (справа).
Зеленые ячейки — различные ячейки в частично совпадающих строках.

А вот если мы выберем второй алгоритм сравнения — по ключевому столбцу, то нам будет предложено указать его. В нашем случае вполне можно ключевым столбцом обозначить «Товар».

После этого мы видим немного другой результат сравнения:

Как видите, основным здесь действительно является факт совпадения значений в столбцах B. Строки, в которых нет такого совпадения, сразу выделяются красным или фиолетовым. А вот если совпадение есть, тогда идем в столбец С и сравниваем записанную там цену. Зелёные ячейки как раз и показывают нам товары, которые имеются в обоих прайс-листах, но цена на них изменилась.

Не знаю как вам, но мне второй вариант представляется более информативным.

А что же дальше делать с этим сравнением?

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

Используя её, вы последовательно просматриваете найденные различия и решаете, объединить их или игнорировать:

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

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

Сохраните внесенные вами изменения и сохраните оставшиеся различия (Save workbooks and keep difference marks),
Сохраните внесенные вами изменения и удалите оставшиеся различия (Save workbooks and remove difference marks),
Восстановите исходные книги из резервных копий (Restore workbooks from backup copies).

Вот как вы можете сравнить два листа в Excel при помощи инструмента сравнения Compare Two Sheets (надеюсь, он вам понравился

Если вам интересно попробовать, полнофункциональная ознакомительная версия доступна для загрузки здесь .

Источник

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

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

Вручную через «точку-с-запятой» в поле «Источник».

Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.

Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

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

Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:

Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.

Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.

Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

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

Делаем активной ячейку, куда хотим поместить раскрывающийся список.
Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

Создадим первый выпадающий список, куда войдут названия диапазонов.

Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.

Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(0, 1)) = 0 Then
            Target.Offset(0, 1) = Target
        Else
            Target.End(xlToRight).Offset(0, 1) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
        Else
            Target.End(xlDown).Offset(1, 0) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) <> 0 And oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).

Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.

Жмем «Свойства» – открывается перечень настроек.

Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

Скачать пример выпадающего списка

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

Источник

Содержание

Выполнение выборки
- Способ 1: применение расширенного автофильтра
- Способ 2: применение формулы массива
- Способ 3: выборка по нескольким условиям с помощью формулы
- Способ 4: случайная выборка
Вопросы и ответы

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

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

Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».
Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».

Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

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

Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно». В поле справа устанавливаем значение «04.05.2016». В нижнем блоке устанавливаем переключатель в позицию «До или равно». В правом поле вписываем значение «06.05.2016». Переключатель совместимости условий оставляем в положении по умолчанию – «И». Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK».

Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».

Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

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

Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо». В первом блоке переключатель условий устанавливаем в позицию «Равно». В поле справа от него вписываем слово «Картофель». Переключатель нижнего блока так же ставим в позицию «Равно». В поле напротив него делаем запись – «Мясо». И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ». Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK».

Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

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

Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

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

Урок: Функция автофильтр в Excel

Способ 2: применение формулы массива

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

На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:
=ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Жмем сочетание клавиш Ctrl+Shift+Enter.

Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:
=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Опять набираем сочетание клавиш Ctrl+Shift+Enter.

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

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

В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…».

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

В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

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

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

Урок: Условное форматирование в Excel

Способ 3: выборка по нескольким условиям с помощью формулы

Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.

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

Как и в предыдущем способе, поочередно выделяем пустые столбцы новой таблицы и вписываем в них соответствующие три формулы. В первый столбец вносим следующее выражение:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

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

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

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

Способ 4: случайная выборка

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

Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
=СЛЧИС()

Эта функция выводит на экран случайное число. Для того, чтобы её активировать, жмем на кнопку ENTER.

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

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

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

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

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

После этого все значения таблицы выстраиваются в порядке возрастания или убывания случайных чисел. Можно взять любое количество первых строчек из таблицы (5, 10, 12, 15 и т.п.) и их можно будет считать результатом случайной выборки.

Урок: Сортировка и фильтрация данных в Excel

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Самый быстрый ВПР

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

В этом случае, правильный выбор функции, применяемой для связывания таблиц, играет решающую роль — разница в производительности между ними, как мы увидим далее, может составлять более 20 раз!

Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.

Так что пришла пора снова взяться за секундомер и выяснить — кто же самый быстрый. Ну и, заодно, проверить — какие способы поиска и подстановки данных в Excel вы знаете 🙂

Подопытный кролик

Тест будем проводить на следующем примере:

Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача — подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.

Способ 1. ВПР

Сначала — классика 🙂 Легендарная функция вертикального просмотра — ВПР (VLOOKUP) , которая приходит в голову первой в подобных ситуациях:

ВПР

B2 — искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
$G$2:$H$600 — закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
2 — номер столбца в прайс-листе, откуда мы хотим взять цену
0 или ЛОЖЬ — переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

Время вычисления = 4,3 сек.

Способ 2. ВПР с выделением столбцов целиком

Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ( $G$2:$H$600 ), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

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

Время вычисления = 14,5 сек.

Способ 3. ИНДЕКС и ПОИСКПОЗ

Следующей после ВПР ступенью эволюции для многих пользователей Microsoft Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) . Выглядит эта формула так:

Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

Что нужно найти — название товара из B2
Где мы это ищем — столбец с названиями товаров в прайсе ( $G$2:$G$600 )
Режим поиска: 0 — точный, 1 или -1 — приблизительный с округлением в меньшую или большую сторону, соответственно.

Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).
Можно извлекать данные, которые находятся левее столбца, где просходит поиск.

По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

Время вычисления = 7,8 сек.

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

. то результат получается совсем печальный:

Время вычисления = 28,5 сек.

28 секунд, Карл! В 6 раз медленнее ВПР!

Способ 4. СУММЕСЛИ

Если нужно найти не текстовые, а именно числовые данные (как в нашем случае — цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF) . Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:

Первый аргумент СУММЕСЛИ — это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ( $G$2:$G$600 ).
Второй аргумент ( B2 ) — что мы ищем.
Третий аргумент — диапазон ячеек с ценами $H$2:$H$600 , числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.

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

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

Время вычисления = 12,8 сек.

При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:

Время вычисления = 41,7 сек.

Это самый плохой результат в нашем тесте.

Способ 5. СУММПРОИЗВ

Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂

Суть этого метода заключается в использовании функции СУММПРОИЗВ (SUMPRODUCT) , изначально предназначенной для поэлементного перемножения нескольких диапазонов с последующим суммированием полученных произведений. В нашем случае, вместо одного из массивов будет выступать условие, а вторым будут цены:

Выражение ($G$2:$G$600=B2) , по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE ) или ЛОЖЬ (FALSE) , что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.

Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl + Shift + Enter , т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы — не очень:

Время вычисления = 11,8 сек.

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

Способ 6. ПРОСМОТР

Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР — это использование функции ПРОСМОТР (LOOKUP) . Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) — про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

B2 — название груза, которое мы ищем
$G$2:$G$600 — одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
$H$2:$H$600 — такого же размера диапазон, откуда нужно вернуть найденный результат (цену)

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

Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГ Е ДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

Время вычисления = 7,6 сек.

Способ 7. Новая функция ПРОСМОТРХ

Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником — функцией ПРОСМОТР (LOOKUP) . Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

Время вычисления = 7,6 сек.

Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

. то скорость падает до совершенно неприличных уже значений:

Время вычисления = 28,3 сек.

А если на динамических массивах?

Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:

Разница с классическим вариантом в том, что первым аргументом ВПР здесь выступает не одно искомое значение (а формулу потом нужно копировать вниз на остальные строки), а сразу весь массив из полумиллиона грузов B2:B500000, цены для которых мы хотим найти. Формула при этом сама распространяется вниз, занимая требуемое количество ячеек.

Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила — пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

Время вычисления = 1 сек.

Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми — время вычислений не больше 1 секунды! Фантастика.

А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁

Что с умными таблицами?

Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и «умными» таблицами. Я имею ввиду те самые «красивые таблицы», в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .

Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

[@Груз] — ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
Таблица2 — ссылка на прайс-лист

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

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

Время вычисления = 1 сек.

У меня есть подозрение, что дело тут не в самих «умных» таблицах, а всё в том же обновлении вычислительного движка, т.к. на старых версиях Excel такого прироста в скорости на умных таблицах я не помню.

Бонус. Запрос Power Query

Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?

Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range) .
После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) .
После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge) :

В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .

Время обновления = 8,2 сек.

Итоговая таблица и выводы

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

Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:

ВПР с несколькими условиями: 5 примеров.

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

Мы предложим вам несколько вариантов решения проблемы поиска по нескольким условиям.

ВПР по нескольким условиям с использованием дополнительного столбца.

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

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

Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.

Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7

и копируем в находящиеся ниже ячейки.

Формула поиска в D4 будет выглядеть:

В диапазон поиска включаем и наш дополнительный столбец. Критерий поиска – также объединение 3 значений. И извлекаем результат из 5 колонки.

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

Вид станет приятнее, а на результаты это никак не повлияет.

ВПР по двум условиям при помощи формулы массива.

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

Для этого в верхней части нашего листа запишем критерии поиска: дата и магазин. В ячейке B3 будем выводить сумму выручки.

Формула в B3 выглядит следующим образом:

Обратите внимание на фигурные скобки, которые означают, что это формула массива. То есть наша функция ВПР работает не с отдельными значениями, а разу с массивами данных.

Разберем процесс подробно.

Мы ищем дату, записанную в ячейке B1. Но вот только разыскивать мы ее будем не в нашем исходном диапазоне данных, а в немного видоизмененном. Для этого используем условие

То есть, в том случае, если наименование магазина совпадает с критерием в ячейке B2, мы оставляем исходные значения из нашего диапазона. А если нет – заменяем их на пробелы. И так по каждой строке.

В результате получим вот такой виртуальный массив данных на основе нашей исходной таблицы:

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

С такой работой функция ВПР вполне справится.

Такой ход стал возможен путем применения формулы массива. Поэтому обратите особое внимание: круглые скобки в формуле писать руками не нужно! В ячейке B3 вы записываете формулу

И затем нажимаете комбинацию клавиш CTRL+Shift+Enter. При этом Excel поймет, что вы хотите ввести формулу массива и сам подставит скобки.

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

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

Вот как это будет выглядеть.

ВПР по нескольким критериям с применением массивов — способ 2.

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

Условия возьмем те же, что и в предыдущем примере.

Формулу в С4 введем такую:

Естественно, не забываем нажать CTRL+Shift+Enter.

Теперь давайте пошагово разберем, как это работает.

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

Как и в предыдущем примере, мы ищем текст из объединенных в одно целое условий поиска.

Далее определяем данные, среди которых будем искать.

Конструкция вида A7:A20&B7:B20&C7:C20;D7:D20 создает 2 элемента. Первый – это объединение колонок A, B и C из исходных данных. Если помните, то же самое мы делали в нашем дополнительном столбце. Второй D7:D20 – это значения, одно из которых нужно в итоге выбрать.

Функция ВЫБОР позволяет из этих элементов создать массив. как раз и означает, что нужно взять сначала первый элемент, затем второй, и объединить их в виртуальную таблицу – массив.

В первой колонке этой виртуальной таблицы мы будем искать, а из второй – извлекать результат.

Таким образом, для работы функции ВПР с несколькими условиями мы вновь используем дополнительный столбец. Только создаем его не реально, а виртуально.

Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

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

Формула в С4 теперь выглядит так:

И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.

Теперь давайте разбираться, как это работает.

Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.

Синтаксис здесь следующий:

ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска)

Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.

Выражение (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:

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

Убеждаемся, что цифра 1 встречается только единожды.

При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.

Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.

Таким образом мы выбрали значение по нескольким условиям без использования функции ВПР.

Достойная замена – функция СУММПРОИЗВ.

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

Работает как формула массива, хотя по факту таковой не является. В этом заключается замечательное свойство функции СУММПРОИЗВ, о которой мы еще много будем говорить в других статьях.

Последовательно по каждой строке диапазона от 2-й до 27-й она проверяет совпадение каждого соответствующего значения с критерием поиска. Эти результаты перемножаются между собой и в итоге еще умножаются на сумму выручки. Если среди трех условий будет хотя бы одно несовпадение, то итогом будет 0. В случае совпадения сумма выручки трижды умножится на 1.

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

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

Для этого из формулы просто уберем сравнение по дате.

Кстати, возможен и другой вариант расчета с этой же функцией:

Итак, мы рассмотрели примеры использования функции ВПР с двумя и с несколькими условиями. А также обнаружили, что этой ценной функции есть замечательная альтернатива.

Примеры использования функции ВПР:

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

2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

Решение2 : Для отбора строк можно использовать формулы массива, аналогичные Задаче2 (т.е. формулы массива, возвращающие несколько значений ):

Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание . Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список .

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел , т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив . Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел , получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив .

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

В разделе Отбор на основании повторяемости собраны статьи о запросах с группировкой данных. Из повторяющихся данных сначала отбираются уникальные значения, а соответствующие им значения в других столбцах — группируются (складываются, усредняются и пр.).

Наиболее популярные статьи из этого раздела:

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

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

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

Остальная часть формулы аналогична рассмотренным выше.

12. Актуальная цена

Пусть для товара ежедневно заполняется таблица цен (цена может меняться, но не каждый день). Нужно найти актуальнуй цену, т.е. цену на последнюю дату. Если товар всего один, то можно отсортировать по дате и в последней строке будет нужная актуальная цена. Если товаров много, то нужно сначала выбрать Автофильтром нужный товар, затем опять отсортировать по цене.

Чтобы иметь перечень товаров с актуальными ценами придется использовать формулы:

2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).

Источник

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

Смотрите также из тех, гдекарандаши=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) точках: же присваивает новый (постоянный)не тот, буду: Вторая лучше в данные – «Хлеб». дням.. Производим клик по«OK» указанной области будутследует ввести адресСЧЁТЕСЛИМН( диапазон поиска содержит

excel2.ru

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

отражены в окне. строки, в которой. Она также относится«равно» повторяющиеся значения, то – выбрать из

Задача

БумагаручкиПОИСКПОЗ магазине, заданном пользователем. ячеек. Новый формат такое 100000 -=ИНДЕКС(C6:C101;B3+ЕСЛИ(B2=»м»;48)-17)

Решение

F3 (сумма) пишем

«Главная»«OK»Запускается окно аргументов функции
Далее устанавливаем курсор в расположены данные по
к статистической группе), второй столбец из

исходной таблицы строки,, т.к. ЛОЖЬ функцией), а диапазоном дляищет в столбце В ячейке А8 заранее определен пользователем значение в формуле?SerjVorotilov формулу, которая посчитает. Клацаем по пиктограмме.СУММЕСЛИ поле выручке Магазина 1

операторов. Задачей	«<>» таблицы выше поясняет
удовлетворяющие определенным критериям	МИН выборки — столбецD1:D13
можно изменить номер	в параметрах правила подскажите…плиз…: Смотри прикрепленный файл.
все заказы Иванова	«Условное форматирование»Производится запуск окошка аргументов. В нём имеется
«Условие1»	за неделю. ДляСЧЁТЕСЛИМН(
какое значение будет	(подобно применению стандартногоигнорируется. При желании, с ценами.значение артикула из торговой точки –ВЫБОР условного форматирования (зеленая
davaispoem	Условия задачи:
на хлеб. Формула	, размещенной в блоке
оператора, наименование которого	три поля, соответствующих

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

1. В таблице такая.«Стили» было указано выше. аргументам указанного оператора. указать нижнюю границу в поле и в указанном массиве,). Например, если задать первое значение, удовлетворяющее из исходной таблицы всю функцию ЕСЛИ(…) исходный диапазон сC16 СУММ ссылку наФункция ВЫБОР находит и протянулось, большое спасибо 1 столбец B1:B5

=СУММ((A2:A9=F2)*(B2:B9=F1)*C2:C9)на ленте. ОткрываетсяУстанавливаем курсор в полеВ поле значений в ячейках, выделяем соответствующую строку которые удовлетворяют определенному выражение

 критерию). с помощью формул в строке формул ценами в «умную. Последний аргумент функции другой интервал. Если возвращает значение из вам. и строка C3:G3В первых скобках список действий. Клацаем«Диапазон суммирования»«Диапазон»

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

excel2.ru

Использование критериев в Microsoft Excel

массива. В отличие… и нажать на таблицу». Для этого 0 — означает поставить в ячейке списка аргументов, используяGuest = const, а формулы указан диапазон в нём по. В отличие отвводим область таблицы, участие в подсчете. отображаются в окне. тот факт, что будут учитываться только повторяющиеся значения и

от применения Фильтра клавиатуре

Применение критериев

выделите его и поиск точного (а А8 цифру 2, номер индекса. Может: Максимально возможное число. диапазон C4:G8 заполняется всех товаров, из позиции последующих аргументов, этот в которой будут Указываем выражениеУчитывая, что для Магазина можно задать не элементы, заданные аргументом требуется вернуть не (F9 выберите на вкладке не приблизительного) соответствия. формула подсчитает выручку обработать до 254 Для поиска последней пользователем, при этом которого нужно выбрать«Создать правило…» единственный в своем располагаться значения, проверяемые

СЧЁТЕСЛИ

«>14000» 1 дневная норма один, а несколько«Диапазон» одно, а всеCTRL+SHIFT+L, чтобы наглядно увидетьГлавная — Форматировать как

Функция выдает порядковый

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

 на соблюдение условий.. выручки составляет 14000 параметров, и отличает, в которых находятся значения, удовлетворяющие критерию,или Данные/ Сортировка тот самый результирующий таблицу (Home - номер найденного значения (результат СУММ для синтаксис, но достаточноПочему 100000? Дата столбце значения (в ячейке F2 (хлеб).Активируется окошко генерации правила на тот массив В нашем случаеВ поле рублей, то в этого оператора от числовые значения менее то читайте статью и фильтр/ Фильтр) массив, из которого Format as Table) в диапазоне, т.е. диапазона В2:В5). широкие возможности. Рассмотрим в числовом выражении данном примере -Во вторых скобках форматирования. В области значений, где будет это будет строка«Диапазон условия2» поле предыдущего. Синтаксис следующий: 50. Использование данных Запрос на основе отобранные строки будут потом функцияили нажмите фактически номер строки,С помощью функции ВЫБОР

лучшие из них пятизначна (например, 01.01.12=40909), от 1 до указан диапазон всех

выбора типа правила производиться суммирование подошедших дат. Ставим курсорвводим тот же«Условие 1»=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…) знаков для указания Элементов управления формы. помещены в отдельнуюМИН

Ctrl+T где найден требуемыый можно задать аргумент на конкретных практических а задача функции 5) не повторяются. заказчиков, из которого
выделяем наименование под указанные критерии в данное поле адрес тем жевписываем выражение«Диапазон условия» параметров будут актуальнымиСовет таблицу.и выбирает минимальное. Наша «поумневшая» таблица артикул.
для функции СУММ примерах. ПРОСМОТР() в данном2. В таблице нужно выбрать заказчика,«Форматировать только ячейки, которые данных. Затем выделяем и выделяем все способом, который вводили«>14000»является идентичным первому и для всех: Если в диапазонДля нахождения позиции значения значение: автоматически получит имяФункция так, чтобы получитьСинтаксис функции: =ВЫБОР(номер индекса; случае — найти 2 ячейка J3
написанного в ячейке содержат» область строки ячейки, в которых в поле. аргументу предыдущего оператора. других вариантов, о поиска постоянно вводятся в столбце, сЭтот вариант использует малоизвестнуюТаблица1ИНДЕКС результат подсчета 2,

знач. 1; знач. дату в строке позволяет из раскрывающегося F1 (Иванов).. В первом поле
«Магазин1» содержатся даты.«Диапазон условия1»В поля То есть, он которых пойдет речь новые значения, то последующим выводом соответствующего (и многими, к, а к столбцамвыбирает из диапазона 3, 4 и 2; …). 5. списка выбрать определеннуюВ третьих скобках блока условий из, в которой размещеныТак как нам нужно, то есть, опять

«Диапазон условия2 (3,4,5)» представляет собой ссылку

СЧЁТЕСЛИМН

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

ниже.

 дубликатов следует наложить столбца в EXCEL,ДМИН (DMIN) обращаться по ихзначение, находящееся на диапазона:Номер индекса – порядковый G можно было в интервале значенийНО, нажимаем не выбираем соответствующей торговой точке.

 выручки, начиная с со значениями выручки строк с недельной которой будет производитьсяА теперь давайте на определенные ограничения (см. существует специальная функцияиз категории именам, используя выражения пересечении заданной строкиФормула суммирует диапазон А1:А4. номер выбираемого аргумента дать прямую ссылку строки C3:G3. просто «Enter», а«Значение ячейки»После того, как адрес 11 марта, то

по первой торговой выручкой соответственно Магазина подсчет ячеек, удовлетворяющих конкретном примере посмотрим, статью Ввод неповторяющихся ВПР(), но дляРабота с базой данных типа (номер строки с Вторая часть диапазона из списка значений. на G4. Для3. В таблице ТРИ кнопки: «Ctrl». В следующем поле отобразился в окне, в поле точке. 2, Магазина 3, указанным условиям. Данный

как работает данный значений). Для визуальной ее решения можно (Database)Таблица1[Товар] артикулом выдает функция функции СУММ задана Может быть числом следующего столбца Н4=»», 2 диапазон I4:I8 + «Shift» + выбираем позицию

переходим к полю«Критерий»
В поле Магазина 4 и
оператор позволяет задать оператор на практике.
проверки наличия дубликатов использовать также и
и требует небольшогоили

ПОИСКПОЗ с помощью функции от 1 до поэтому нужно найти строго соответствует диапазону «Enter». Только тогда«Больше»«Диапазон условия1»вбиваем значение
«Условие2» Магазина 5. Действие сразу несколько такихИтак, имеется таблица, где можно использовать Условное другие функции. Рассмотрим изменения результирующей таблицы:Таблица1[Цена]) и столбца (нам ВЫБОР. 254, ссылкой на ячейку, в которой J4:J8, то есть будет формула считать.. В последнем —
. Тут нам нужно«>10.03.2017»указываем верхнюю границу выполняем по тому областей.
представлена выручка по форматирование (см. статью задачу в случаеКак видите, зеленые ячейки. При желании, стандартное нужен регион, т.е.Данная функция хорошо обрабатывает ячейку с числом нужная дата. Вот значению I4 строго Вокруг этой формулы указываем само значение,

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

имя второй столбец). в качестве значений от 1 до здесь приходит на соответствует J4, I5 появятся фигурные скобки. больше которого требуется строки с датами.В поле«. и для первого

представляет собой критерий, неделю. Нам нужноДля организации динамической сортировкиПусть в диапазоне из столбца вТаблица1enzo простые списки чисел. 254, массивом или помощь ПРОСМОТР(). — J5, и Так обозначаются формулы отформатировать элементы таблицы. Производим зажим левой«Диапазон суммирования»После того, как все аргумента данной группы. который определяет, какие узнать количество дней пополняемого диапазона поискаА4:В15

строку и надможно подкорректировать на: Уважаемые, добрый день! Поэтому с ее формулой.vikttur т.д.
массива Excel. Подробнее У нас это кнопки мыши инужно указать область, указанные действия произведены,В поля элементы из соответствующего за этот период, можно использовать идеиимеется таблица с ними добавлена мини-таблица

вкладке Есть у меня помощью можно вычислитьЗнач. 1; знач. 2;: Это я выше.Вопрос: о формулах массива, 14000. Чтобы выбрать

выделяем все даты значения которой, отвечающие клацаем по кнопке«Условие2» массива данных войдут в которых в из статьи Сортированный перечнем сотрудников и
(F4:H5) с условиями.Конструктор (Design) таблица перечня позиций. по номеру месяца … — список Выпал.Подскажите, пожалуйста, формулу,  читайье в статье тип форматирования, клацаем в таблице. указанным критериям, будут«OK», в подсчет, а Магазине 2 доход список.
их зарплат (фамилии Логика работы этой, которая появляется, если Я беру фильтром финансовый квартал. аргументов от 1В таблице находятся данные возвращающую: «Формулы массива Excel». по кнопкеСтавим курсор в поле суммироваться. В нашем.«Условие3» какие не войдут. от реализации превысилПрограмма Microsoft Excel является сотрудников не повторяются). функции следующая:
щелкнуть в любую выбираю определенное условиеТаблица с номерами месяцев до 254, из по продажам отсортированные1. В ячейку Получилось так.«Формат…»«Условие1» случае это значенияПрограмма выдает результат расчета.

, Каждой заданной области 15000 рублей. не просто табличнымТребуется, введя в ячейкуБаза_данных ячейку нашей «умной» (столбец постоянный к и кварталов: которого выбирается значение по возрастанию относительно К4 — фамилию

Так можно выбирать и.. Первым условием является выручки строки Как видим, итоговое

«Условие4» данных нужно указыватьВыделяем элемент листа, в редактором, а ещёD4
- вся наша таблицы. Подробнее о примеру «Группа крови»Так как финансовый год или действие, соответствующее даты транзакции. Необходимо из диапазона B1:B5, складывать любые данныеАктивируется окно форматирования. Передвигаемся то, что нами

СУММЕСЛИ

«Магазин1» значение равно 5.и условие отдельно, даже который оператор будет и мощнейшим приложениемфамилию сотрудника, вывести таблица вместе с таких таблицах и = 2) , начался в апреле,

номеру индекса. Первое

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

 заголовками. их скрытых возможностях и вывожу список месяцы 4, 5 значение – обязательный дни сумма транзакции даты, выбираемой пользователем диапазона.«Заливка» не ранее 09 элементов листа. в 5 днях

вносим соответственно значения если оно совпадает. После этого щелкаем Не в последнюю его зарплату. РешениеПоле можно почитать здесь. для распечатки. Делал и 6 попали аргумент. Последующие – превысила 5000. Следует в ячейке J3,В Excel есть. Из предложенных вариантов

марта. Поэтому вводимПосле того, как произведено из исследуемых семи«>15000 Обязательно требуется, чтобы по пиктограмме очередь такая возможность приведено в файле- название столбцаНачиная с версии Excel

это фильтром , в первый квартал. нет. Список аргументов-значений выбрать все суммы и значения ячейки функции, которые считают
цветов заливки выбираем значение введение всех указанных выручка в первом», все массивы, используемые«Вставить функцию» появилась благодаря встроенным примера. из шапки таблицы,
2016 в наборе потом копировал значения При введении аргументов – числа, ссылки по условию и
I4, находящегося в не все ячейки, синий, щелкая по«>08.03.2017» данных, жмем на магазине была в«>24000» в качестве областей. функциям. С помощьюАлгоритм решения задачи следующий: из которого выбирается функции Microsoft Excel

удалял столбцы… Попросили функции, номера кварталов на ячейки, имена, выделить их цветом. Таблице 1 в а выборочно, по нему левой кнопкой. кнопку

интервале от 14000, условия, имели одинаковоеПроизводится запуск некоторых функций (операторов)находим в списке кодов минимальное значение. наконец появились функции, автоматизировать)) Хотят чтобы необходимо вводить в формулы, функции или

Вместо того чтобы тратить столбце выбранной даты; условию, которое мы мыши. После того,Перемещаемся к аргументу«OK»
до 17000 рублей.«>11000» количество строк иМастера функций можно задавать даже значение, совпадающее сКритерий которые легко решают была ячейка «Группа том порядке, в текст. время на визуальный2. В ячейку напишем в формуле. как выбранный цвет

СУММЕСЛИМН

«Диапазон условия2».Ещё одним оператором, которыйи столбцов.. Совершаем перемещение в условия вычисления, которые критерием;- таблица с нашу задачу -

крови", куда напишут

 каком они находятсяЕсли указать номер индекса анализ данных таблицы, К4 — фамилию Например, посчитать сумму отобразился в области

. Тут нужно внестиПосле этого в предварительно использует критерии, является«>32000»Для того, чтобы задать

 блок принято называть критериями.определяем номер позиции (строку) условиями отбора, состоящая

это функции цифру 2 и в таблице.

1, то функция будем использовать правило из диапазона B1:B5, заказа определенного покупателя,«Образец» те же координаты, указанный элемент рабочегоСУММЕСЛИ. Как нетрудно догадаться, несколько параметров одной«Статистические» Давайте подробнее узнаем, найденного значения; (минимально) из двухМИНЕСЛИ (MINIFS) из таблицы будетВ ячейку D8 пользователь

вернет первое значение условного форматирования, которое получающуюся на пересечении итоги конкретного ученика,, клацаем по кнопке
которые были записаны листа будет выведен. В отличие от эти значения соответствуют и той же. Там находим и каким образом можновыводим значение из соседнего ячеек: названия столбца,и вытягиваться все позиции вводит номер месяца.
их перечня. Если автоматически определит и даты, выбираемой пользователем
посчитать данные за«OK» в поле результат обработки данных предыдущих функций, он интервалу выручки, превышающую области данных, например, выделяем наименование их использовать при столбца той же по которому идетМАКСЕСЛИ (MAXIFS) с группой 2, В ячейке D9 индекс равен 2 выделит цветом все

в ячейке J3, определенную дату, т.д..«Диапазон условия1» функцией относится к математическому норму для соответствующего чтобы подсчитать количество«СЧЁТЕСЛИ» работе в Экселе. строки.

проверка (. Синтаксис этих функции но с меньшим функция ВЫБОР вычисляет – второе значение. необходимые суммы. Пример и значения ячейки Про одну изАвтоматически происходит возврат к. Делаем это тем

СУММЕСЛИ блоку операторов. Его магазина. ячеек, в которых. Затем следует клацнутьСкачать последнюю версиюРешение практически аналогично поискуТовар очень похож на определенными столбцами. номер финансового квартала.

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

по кнопке Excel числового значения из) и критерия (СУММЕСЛИМН (SUMIFS)Пока ломаю головуМожно так же вычислять Если список аргументов

на рисунке: Таблице 1 в в статье «Функция форматирования. В нём есть, путем выделения он равен 47921,53. данных в ячейках, произведен ввод всех определенного числа, но«OK»Критерии представляют собой условия, статьи Поиск позицииБумага, Карандаши, Ручки

: как можно сделать грядущие даты. Эту состоит из конкретных
Все фактуры отсортированные относительно столбце выбранной даты; «СУММЕСЛИ» в Excel». также в области строчки с датами. Это означает, что которые соответствуют определенному необходимых данных (всего меньше другого числа,. при которых программа ЧИСЛА с выводом

Условное форматирование

).=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; это… задачу она решает значений, то формула дат транзакций.и т.д.В Excel можно«Образец»Устанавливаем курсор в поле

начиная с 11.03.2017, условию. Синтаксис таков: 10 полей), жмем следует в качестве

Происходит активация окна аргументов выполняет определенные действия. соответствующего значения изЭто обычная формула (не Условие2 … )
китин в совокупности с ВЫБОР возвращает одноЧтобы автоматически экспонировать цветомТо есть, дата, написать ссылки наотображается синий цвет.«Условие2» и до конца=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования]) на кнопку аргумента
вышеуказанного оператора. В Они применяются в соседнего столбца. Для формула массива), т.е.где: а файл с функцией ДЕНЬНЕД. Например, из значений согласно необходимые суммы, необходимо: выбираемая пользователем в несколько листов в Тут нам нужно. Вторым условием является анализируемого периода, общаяАргумент«OK»«Диапазон условия» поле целом ряде встроенных этого типа задач можно вводить иДиапазон_чисел группой крови на пользователь делает небольшие
индексу.Выделите диапазон ячеек A2:C14 ячейке J3, определяет большой формуле быстро. произвести одно единственное то, что дни, выручка по Магазину«Диапазон».несколько раз указать«Диапазон» функций. В их в EXCEL существует использовать ее привычным
- диапазон с рукаве? отчеты о проделаннойЕсли аргументы – ссылки и выберите инструмент: номер столбца, в Например, сложить данные действие: клацнуть по за которые будет 1 составила 47921,53указывает на областьПрограмма производит подсчет и
один и тотследует указать область названии чаще всего специальная функция ВПР(), образом. Кроме того, числами, из которых

Udik работе и сдает на ячейки, то

«ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». котором ищется значение

отчетов, расположенных на кнопке суммироваться выручка, должны рубля. ячеек, которые будут выводит результат на же массив. Но ячеек, среди которых присутствует выражение но для ее в той же выбирается минимальное или: без файла сложно их начальнику каждый функция вернет ссылки.В появившемся окне «Создание ячеек диапазона I4:I8, разных листах. Подробнее,«OK» быть не позже

Завершим изучение операторов, которые

lumpics.ru

Сложить выборочно данные из таблицы Excel.

проверяться на соблюдение экран. Как видим, при этом в будет производиться подсчет.«ЕСЛИ» решения можно использовать категории можно найти максимальное отвечать. А так вторник. Можно рассчитатьВЫБОР возвращает ссылку на правила форматирования» выберите нужное для возвращения смотриет в статье.
13 марта. Поэтому используют критерии, остановившись условия. По сути, он равен числу
качестве соответствующих аргументов В нашем случае. К данной группе и другие функции функцииДиапазон_проверки — например, расширенный
дату следующего вторника. интервал В1:В7. А опцию: «Использовать формулу конкретной фамилии в «Ссылки в ExcelПосле выполнения последнего действия,
записываем следующее выражение:
на функции он задается по 3. Это означает,«Условие» следует выделить содержимое операторов, прежде всего,
(про функцию ВПР()БДСУММ (DSUM)- диапазон, который фильтр.В первом столбце вспомогательной функция СУММ использует
для определения форматированных соответствующий диапазон К4:К8
на несколько листов все ячейки выделенного«.СУММЕСЛИМН тому же принципу, что в трехследует указывать разные строки нужно отнести см. эту статью)., проверяется на выполнениеenzo таблицы – номера
этот результат в ячеек».Guest сразу».
массива, где содержитсяПереходим в поле. Задачей данной математической что и одноименный днях из анализируемой критерии.«Магазин 2»СЧЁТЕСЛИРешениеДМАКС (DMAX) условия: Извиняюсь))) прикладываю! дней недели. В качестве аргумента.В поле ввода введите
:PerineiKa число большее, чем«Диапазон условия2» функции является суммирование аргумент функции недели выручка воНа примере все той, в которой расположены,Если несколько значений удовл.

excel-office.ru

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

,Условиеsboy
третьем столбце –Аргументы-значения могут быть представлены
формулу: 5000′ class=’formula’>Микки: Доброго времени, уважаемые 14000, будут залиты. В данном случае значений указанных областейСЧЁТЕСЛИ всех торговых точках
же таблицы с значения выручки поСЧЁТЕСЛИМН критериюБСЧЁТ (DCOUNT)- критерий отбора: количество дней, которое отдельными значениями:Нажмите на кнопку формат,
: Вам задали Вы Форумчане! синим цветом.

 нам нужно выделить таблицы, отобранных по

. превышала установленную для
недельной выручкой магазинов дням. Ставим курсор,=ВПР($D$4;A4:B15;2;ЛОЖЬ), которые используются совершенноНапример, в нашемenzo
нужно прибавить кОсобенности использования функции: чтобы задать цвет

 и решайте ВыПрошу неучу в

Более подробно о возможностях тот самый массив, нескольким параметрам. Синтаксис«Критерий» них норму. посмотрим, как это в указанное полеСУММЕСЛИберется аналогично, но умеют случае:

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

 же не 35 проблеме:
условного форматирования рассказывается
адрес которого был указанного оператора таков:— является обязательнымТеперь несколько изменим задачу. работает. Нам нужно и, зажав левую,первое находить не толькоПросто, красиво, изящно. Одна улыбался от души)
получить следующий вторник. то функция возвращает например – зеленый. девчонок файл неиз приложенной таблицы в отдельной статье. внесен, как массив
=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…) аргументом, задающим параметр Нам следует посчитать узнать количество дней кнопку мыши, выделяемСУММЕСЛИМНсверху минимум, но и
проблема — функции
почему у всех Например, к понедельнику
меньшее целое значение. И нажмите на открывал но похоже по 2 признакамУрок: Условное форматирование в суммирования.«Диапазон суммирования» отбора ячеек из количество дней, в недели, когда доход
соответствующий массив в. Кроме встроенных операторов,=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1) сумму, максимум иМИНЕСЛИ телка Маша? необходимо добавить 1Если индекс – массив всех открытых окнах на ИНДЕКС(ПОИКПОЗ();ПОИСКПОЗ();;) Ройте
(в моем случае

программе ЭксельПосле того, как адрес— это аргумент, указанной области данных, которых Магазин 1 во всех указанных таблице. Адрес выделенного критерии в Excelберется

 количество значений пои

китин день, ко вторнику значений, то функция кнопку ОК.SerjVorotilov по возрасту иКак видим, с помощью

 указанного массива отобразился являющийся адресом того которые будут суммироваться. получил выручку, превышающую торговых точках достигал массива отобразится в
используются также припервое условию.МАКСЕСЛИ
: формула массива – 7 дней ВЫБОР вычисляет каждый
В результате мы получили: И, все же, полу)найти значение тариф инструментов, использующих при в окне, переходим массива, ячейки в Принципы указания те 14000 рублей, но
установленной для них окне. условном форматировании. РассмотримсверхуЕсли в исходной таблицепоявились только начиная
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(Таблица3[имя];НАИМЕНЬШИЙ(ЕСЛИ(Таблица3[группа крови]=$R$1;СТРОКА(Таблица3[имя])-2);СТРОКА(A1)));»») (до следующего вторника).
аргумент.

 желаемый эффект. Все хотелось бы поподробнее, и перенести в своей работе критерии, к полю

 котором, отвечающие определенному же, что и меньшую, чем 17000 нормы. Нормы выручкиВ следующем поле

 их применение при=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1) очень много строк, с 2016 версииUdikВ ячейку F2 запишемЕсли индекс не совпадает дни, в которых т.к. своего опыта заглавную (ячейка выделена в Экселе можно

«Условие3» критерию, будут складываться. у аналогичных аргументов рублей.

 следующие:«Критерий» работе с различнымиберется
но данные меняются Excel. Если у: Расширеный фильтр с текущую дату (СЕГОДНЯ()). с номером аргумента сумма транзакции превышает для решения вопроса красным красным).
решать довольно разноплановые. Учитывая, что в«Диапазон условия» предыдущих операторов, которыеСтавим курсор в элемент,Магазин 1 – 14000как раз нужно инструментами данного табличногопервое не часто, то

 вас (или тех, макросом А в ячейку

planetaexcel.ru

Как выбрать суммы по условию в Excel

в списке (меньше 5000 выделились зеленым не хватает.Перечитала почти весь задачи. Это может суммировании будут принимать— аргумент, представляющий были рассмотрены нами где будет произведен рублей;

Как выбрать значения по условию в Excel

задать непосредственный параметр процессора более подробно.сверху удобнее будет использовать кто будет потомenzo F3 – формулу 1 или больше цветом.Алгоритм действий понимаю,

форум, но только быть, как подсчет

участие только значения, собой массив данных,

выше. вывод на листМагазин 2 – 15000
отбора. В нашемГлавной задачей оператора=ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2)) сводную таблицу, т.к. работать с вашим
: для расчета даты
последнего значения), тоВ формуле основную задачу а вот как это нашла: я сумм и значений, величина которых превышает проверяемый на соответствие

«Диапазон суммирования» результатов подсчета. Клацаем рублей; случае нужно подсчитатьСЧЁТЕСЛИберется

формула массива и файлом) более старыекитин следующего вторника: функция выдает ошибку берет на себя его представить формулой или не до так и форматирование, 14000 рублей, вносим условию;— это необязательный по пиктограммеМагазин 3 – 24000 только те элементы, относящегося к статистическойпоследнее функция ДМИН могут версии, то придется, Спасибо то чтоИндекс определяется с помощью #ЗНАЧ!. функция =СУММЕСЛИ(). Она — нет.

конца понимаю тонкости,

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

а также выполнение запись следующего характера:«Условие» аргумент. Он указывает«Вставить функцию» рублей; таблицы, в которых группе, является подсчетсверху сильно тормозить Excel. шаманить другими способами. нужно)! функции ДЕНЬНЕД, которая суммирует только теТ.е., формула изначально ну или просто многих других задач.«>14000»— аргумент, представляющий на конкретную область

exceltable.com

Функция ВЫБОР в Excel ее синтаксис и примеры использования

над рабочей площадьюМагазин 4 – 11000 значение превышает 15000. занятых различными значениями=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)Установите активную ячейку вВ английской версии этоenzo возвращает для заданнойФункция ВЫБОР решает задачи значения, которые соответствуют

Аргументы и особенности синтаксиса

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

собой критерий отбора массива, в которой листа. рублей; Поэтому с помощью ячеек, которые удовлетворяютесли столбец отсортирован по любое место нашей будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена])):
даты соответствующего дня по представлению значений определенным критериям в дат C3:G3. для додумать. Простите в данной программеПосле выполнения последнего действия для сложения. будет производиться суммирование.Так как мы совсемМагазин 5 – 32000 клавиатуры вбиваем в определенному заданному условию. возрастанию, то берется

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

рублей. указанное поле выражение Его синтаксис следующий:

последнее выберите на вкладке этой формулы в, не знаю ,Как использовать функцию

Excel. Например, диапазон первом аргументе указывается

даты (равной дате

внятно сформулировала!!! есть, с определенными«OK»
сразу с несколькими и не указывать,СЧЁТЕСЛИМНДля выполнения вышеуказанной задачи,
«>15000»=СЧЁТЕСЛИ(диапазон;критерий)сверху, если нет,Вставка — Сводная таблица первую зеленую ячейку друг отправил, чтобВПР (VLOOKUP)

А2:А8 содержит номера

Функция ВЫБОР в Excel: примеры

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

(Insert — Pivot G4 нажать не сделал я, видимодля поиска и

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

обязательно переходить в рабочего листа, кудаПосле того, как все оператора два аргумента.непредсказуем Table) Enter, а Ctrl+Shift+Enter, Маша его обидела) выборки нужных значений

до 7. Необходимо сравнения со значением под этой датой,

35 девчёнок!!! действие, является набор лист. Он равен применим для решения равен значению обязательного группу будет выводиться итог вышеуказанные манипуляции произведены,«Диапазон»=СУММПРОИЗВ((A4:A15=D4)*(B4:B15)). В появившемся окне чтобы ввести ееВ Microsoft Excel давно из списка мы

отобразить день недели указанном во втором должна искать значениеGIG_ant встроенных функций, а 62491,38. Это означает, задач в контексте аргумента

«Статистические» обработки данных клацаем по кнопкепредставляет собой адрессоответствующие значения суммируются

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

: =СУММПРОИЗВ((A6:A101=B3)*(B6:B101=B2)*C6:C101) также условное форматирование.

что за период нашей таблицы выручки«Диапазон»Мастера функцийСЧЁТЕСЛИМН«OK» массива элементов на=СУММЕСЛИ(A4:A15;D4;B4:B15)ОК Затем формулу можно наборе функции

вы еще с «понедельник», «вторник», «среда», аргументе указываем суммы I4.PerineiKa

Автор: Максим Тютюшев с 09 по от реализации в.. Наименование данного оператора. Клацаем по иконке. листе, в которыхсоответствующие значения суммируются: скопировать на остальные

СЧЁТЕСЛИ (COUNTIF) ней не знакомы «четверг», «пятница», «суббота», для суммирования. ОбратитеНайдя это значение,: ОГРОМНОЕ СПАСИБО!!!!!Можно 13 марта 2017 торговых точках. НамТеперь, как всегда, рассмотрим можно найти в«Вставить функцию»Программа производит подсчет и

следует произвести подсчет.=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)В конструкторе сводной таблицы товары в ячейки, — загляните сюда,

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

exceltable.com

Поиск нужных данных в диапазоне

года сумма выручки нужно будет подсчитать применение данного оператора категории. выводит результат в«Критерий»возвращается ошибка #ЧИСЛО! перетащите поле G5:G6.СУММЕСЛИ (SUMIF) не пожалейте пятиПо такому же принципу

что в первом строки, в которой дня рыла по из ячеек при сложении её доход, который принес на практике. На«10 недавно использовавшихся»Перейдя в элемент листа, который— это аргумент,Для функции ВПР() требуется,Товар

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

и третьем аргументах в параллельном столбце

 сайтам и литературе!

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

например, сумму товарного которых она превышает период с 09 таблицы перед нами щелкаем по кнопке, снова перемещаемся в активацией что именно должны которому производится поиск, а Функция ЕСЛИ проверяети их аналоги,

planetaexcel.ru

Как вывести список из таблицы согласно определенному условию (Формулы/Formulas)

 часов. прописью. адреса ссылок. В которая нам нужна. свою неграмотность, тут чека, т. д. 14000 рублей, составила по 13 марта стоит задача подсчитать«OK» блокМастера функций содержать ячейки указанной был левее столбца,Цену каждую ячейку массива позволяющие искать количество,Если же вы знакомыТеперь рассмотрим можно склонять то время какПостарался написать максимально почитала и решилась У нас есть
62491,38 рубля. 2017 года. При сумму выручки в

.«Статистические». Как видим, в области, чтобы быть

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

 слова с помощью во-втором аргументе применяется

 понятно) задать вопрос….. список товаров, вПоследним, описанным нами, инструментом, этом при суммировании Магазине 1 заОткрывается уже знакомое нам

. В перечне следует данном случае результат включенными в подсчет.

 вывода. Обойти это Чтобы заставить своднуюТовар

 в таблице по — вдогон - Excel. Например, слово смешанная ссылка наСпасибо .

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

excelworld.ru

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

равен числу 5. В качестве параметра ограничение позволяет, например, вычислять не суммуна предмет равенства одному или нескольким стоит разобраться с «рубль»: «0 рублей», ячейку.davaispoem всего доброго в Нам нужно узнать, которым используются критерии, только те дни, 11.03.2017.СЧЁТЕСЛИМНСЧЁТЕСЛИМН Это означает, что может быть использовано

вариант с использованием (или количество), а текущему товару ( условиям. Но что похожими функциями:

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

в выделенном массиве числовое выражение, текст функций ИНДЕКС() и минимум щелкните правойБумага если нужно найтиИНДЕКС (INDEX) рубля», «3 рубля»,Таким образом Excel проверяет раскидать даты поvikttur конкретный заказчик купил Он выполняет указанный превысила 14000 рублей. будет производиться вывод поле выделение. После выполнения в пяти ячейках или ссылка на ПОИСКПОЗ(). Эквивалентная формула кнопкой мыши по). Если это так, не сумму илии «4 рубля», «5 каждую ячейку в месяцам, в диапазон: =ИНДЕКС((C6:C53;C54:C101);B3-17;;(B2=»м»)+1) конкретный товар. У вид форматирования ячеек,Снова выделяем ячейку для результата. Щелкаем по«Диапазон условия1» указанного действия требуется находятся значения превышающие

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

ячейку, в которой приведена в статье любому числу и то выдается соответствующее среднее, а минимумПОИСКПОЗ (MATCH) рублей» и т.д. столбце A и которых они входят?PerineiKa нас есть такая которые отвечают заданным вывода итога и пиктограмме

и, произведя зажим произвести нажатие на

15000. То есть,

 критерий содержится. При о функции ВПР(). выберите в контекстном ему значение из или максимум по
, владение которыми весьмаС помощью функции ВЫБОР берет из нееvikttur
: ОГРОМНОЕ СПАСИБО ЗА таблица.

условиям. Взглянем на клацаем по пиктограмме

«Вставить функцию» левой кнопки мыши, кнопку можно сделать вывод, этом, для указанияЗадача подразумевает, что диапазон меню команду столбца условию(ям)? облегчит жизнь любому можно вернуть ссылку критерии для вычисления: Обратите внимание на ПОМОЩЬ!!!! и первая

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

Мы хотим узнать, сколько пример работы с

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

на диапазон. Это (даты транзакций). Нам диапазон G4:U4 - и вторая формула хлеба заказывал Иванов. условным форматированием..Перейдя в в которых содержится. 2 в пяти следующие знаки: значения. В самом:. В противном случае минимальную цену для Гляньте на следующий

позволяет делать вычисления нужна сумма по форматирование и отсутствие работают!!! Можно снахальничать В пустых ячейкахВыделим те ячейки таблицыВМастер функций выручка по днямВслед за выполнением вышеуказанного днях из анализируемых« ( деле, если критериюВытаскивать данные из сводной

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

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

в блоке Магазина 1. Они алгоритма действий открывается семи выручка превысила«меньше»), удовлетворяет сразу несколько в дальнейшие расчеты ЛОЖЬ (FALSE). базе данных по

Необходимо определить регион поставки по заданному пользователем возвращаемый результат функциейdavaispoem совета- не поделитесьВ ячейке F1 значения за день, прежде всего, выполняем

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

Урок: Мастер функций в( какой строки выводить помощью функцииМИН (MIN)Таким образом, условием будет набранному в ячейку суммирования выручки в является больше чем месяца увидела, формула, «даунов» можно поподробнее – «Иванов», вВыделяем весь массив элементов«Математические» наименование называется. программе Эксель«больше» соответствующее ему значение

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

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA)выбирает минимальное не наименование товара ( C16. заданном пользователем магазине. 5000, то целая работает, но не почитать на тему ячейке F2 пишем

в таблице, в, а там выделяем«СУММЕСЛИ»«Магазин 1»В полеСледующей функцией, которая оперирует), из соседнего столбца?, которую мы подробно из всех значенийбумага

Задача решается при помощиИмеются данные по выручке формула возвращает значение протягивается для следующих создания подобных формул??? наименование товара, по котором указана выручка пункт под названием. Клацаем по кнопке. После этого координаты«Диапазон условия1» критериями, является«=» Если все же разбирали ранее: цен, а только,

двух функций: в нескольких торговых ИСТИНА и сразу ячеек, возможно гдетоvikttur которому хотим посчитать торговых точек по

planetaexcel.ru

«СУММЕСЛИМН»

Источник

Просмотр рядом, чтобы сравнить таблицы.

Сравните 2 книги.

Расположите рядом несколько таблиц Excel.

Сравните два листа в одной книге.

Быстрое выделение значений, которые различаются.

Формула сравнения.

Как произвести сравнение на отдельном листе.

Как сравнить две таблицы при помощи формулы ВПР.

Выделение различий между таблицами цветом.

Сравнение при помощи сводной таблицы.

Сравнение таблиц с помощью Power Query

Как сравнить таблицы при помощи Ultimat Suite для Excel

Создание раскрывающегося списка

Выпадающий список в Excel с подстановкой данных

Выпадающий список в Excel с данными с другого листа/файла

Как сделать зависимые выпадающие списки

Выбор нескольких значений из выпадающего списка Excel

Выпадающий список с поиском

Выполнение выборки

Способ 1: применение расширенного автофильтра

Способ 2: применение формулы массива

Способ 3: выборка по нескольким условиям с помощью формулы

Способ 4: случайная выборка

Еще статьи по данной теме:

Помогла ли Вам статья?

Самый быстрый ВПР

Подопытный кролик

Способ 1. ВПР

Способ 2. ВПР с выделением столбцов целиком

Способ 3. ИНДЕКС и ПОИСКПОЗ

Способ 4. СУММЕСЛИ

Способ 5. СУММПРОИЗВ

Способ 6. ПРОСМОТР

Способ 7. Новая функция ПРОСМОТРХ

А если на динамических массивах?

Что с умными таблицами?

Бонус. Запрос Power Query

Итоговая таблица и выводы

ВПР с несколькими условиями: 5 примеров.

ВПР по нескольким условиям с использованием дополнительного столбца.

ВПР по двум условиям при помощи формулы массива.

ВПР по нескольким критериям с применением массивов — способ 2.

Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

Достойная замена – функция СУММПРОИЗВ.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

7. Один Текстовый критерий (Выбрать Товары определенного вида)

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

9. Два Текстовых критерия (Выбрать Товары определенных видов)

10. Отбор значений с учетом повторов

11. Используем значение критерия (Любой) или (Все)

12. Актуальная цена

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

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

Задача

Решение

Использование критериев в Microsoft Excel

Применение критериев

СЧЁТЕСЛИ

СЧЁТЕСЛИМН

СУММЕСЛИ

СУММЕСЛИМН

Условное форматирование

Сложить выборочно данные из таблицы Excel.

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

Как выбрать суммы по условию в Excel

Как выбрать значения по условию в Excel

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

Функция ВЫБОР в Excel ее синтаксис и примеры использования

Аргументы и особенности синтаксиса

Функция ВЫБОР в Excel: примеры

Поиск нужных данных в диапазоне

Как вывести список из таблицы согласно определенному условию (Формулы/Formulas)

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

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

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

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

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

А вот еще интересные статьи: