Запрос на выборку данных (формулы) в EXCEL

history

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


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

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

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

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

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

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

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

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

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

=ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))-СТРОКА($B$10))

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет возвращать несколько значений ).

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

=ИНДЕКС(B11:B19;НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))-СТРОКА($B$10))

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

Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи.

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

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

Если Вам не удобно использовать формулу массива , которая возвращает несколько значений, то можно использовать другой подход, который рассмотрен в разделах ниже: 5.а, 7, 10 и 11. В этих случаях используются формулы массива, возвращающие одно значение .

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)



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

Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне Е5:Е6 .

Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные.

В отличие от предыдущей задачи создадим два Динамических диапазона : Товары и Цены (без них можно обойтись, но они удобны при написании формул). Соответствующие формулы должны выглядеть в Диспетчере имен ( Формулы/ Определенные имена/ Диспетчер имен ) следующим образом (см. рисунок ниже).

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

=ИНДЕКС(Товары;НАИМЕНЬШИЙ(ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

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

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

=ИНДЕКС(Цены;НАИМЕНЬШИЙ(ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

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

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

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

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

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

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

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

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

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

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

Обратите внимание, что столбец Дат НЕ СОРТИРОВАН.

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

Введите в ячейку D12 формулу массива:

=ИНДЕКС(A$12:A$20;НАИБОЛЬШИЙ(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

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

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

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

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

=СЧЁТЕСЛИМН(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)

Строки исходной таблицы, которые удовлетворяют критериям, выделены также Условным форматированием .

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

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

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

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

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

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

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

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Также в файле примера приведены формулы для условий: Не раньше (не включая); Не позже (включая); Не позже (не включая).

Эта формула введена как формула массив а, возвращающая множество значений (см. здесь Формулы массива в MS EXCEL, возвращающие несколько значений ). Эту формулу можно переделать, чтобы возвращалось только 1 значение , см. следующую задачу 5а.

5а. Один критерий Дата (Выбрать События, которые Закончились/ не начались/ происходят на заданную дату)

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

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

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

= ВЫБОР($C$6;$B$7>C15;И($B$7>=B15;$B$7<=C15);$B$7

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

Для вывода Событий в соседний диапазон используйте формулу:

= ИНДЕКС(A$15:A$21;НАИМЕНЬШИЙ(ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);"");СТРОКА()-СТРОКА($B$14))-СТРОКА($B$14))

Формула делает следующее:

- если Событие удовлетворяет условию, то формула запоминает номер строки этого События: ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);"")

- Функция НАИМЕНЬШИЙ() сортирует полученный массив номеров строк: первыми идут номера строк Событий, которые удовлетворяют критерию;

- Функция ИНДЕКС() выводит названия Событий из указанный строк.

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

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

В отличие от Задачи 5 будем отбирать строки только того Товара, который указан в критерии. Список дат должен быть отсортирован (для каждого из товаров), по товару сортировка не требуется.

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

= ИНДЕКС(A13:A21; НАИМЕНЬШИЙ(ЕСЛИ(($E$7=$A$13:$A$21)*($E$8>=$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 критерия - текст (И) ).

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

= ИНДЕКС($A$11:$A$19; НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19)))))

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

Выражение СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел {1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.

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 критерий).

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

С помощью функции НАИБОЛЬШИЙ() выведем 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. Отбор значений с учетом повторов

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

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

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL

Отбор повторяющихся значений в MS EXCEL

Отбор уникальных значений из двух диапазонов в MS EXCEL

Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL

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

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

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

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

=НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))

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

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

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

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

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

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

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

=ЕСЛИ($C$8="(Все)";НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))

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

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

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

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

1) сначала сформируем перечень товаров без повторов Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

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

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

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

В файле примера приведено решение на листе 12. Актуальная цена.


Комментарии

Только для авторизованных пользователей

Аноним, 1 марта 2016 г.
класс
Аноним, 24 января 2017 г.
В 9 примере: Можно ли добавить 3 или более критериев ???
Михаил, 24 января 2017 г.
Можно, добавьте ваш фильтр в эту часть ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19)+(...)
Аноним, 16 марта 2017 г.
здравствуйте. не получается что то у меня. помогите пожалуйста. два столбца: первый имя- иванов, петров, сидоров и тд, второй столбец- бригада. иванов, петров, сидоров- бригада 1, николаев, семенов, федоров- бригада 2. в другой таблице из выпадающего списка я выбираю бригада 1, и хочу что бы в строках ниже появились фамилии иванов, ниже петров, ниже сидоров. а у меня появляется во всех трех ячейках только иванов. в чем ошибка? спасибо. Дмитрий
Михаил, 16 марта 2017 г.
Выделите несколько ячеек, куда будут выводиться фамилии и нажмите CTRL+SHIFT+ENTER. см. статью http://excel2.ru/articles/formuly-massiva-v-ms-excel-vozvrashchayushchie-neskolko-znacheniy
Аноним, 15 апреля 2017 г.
Спасибо огромное! Очень помог пункт 9 - выбор по нескольким текстовым критериям. Не знала, что критерии соединяются знаком +
Аноним, 15 октября 2017 г.
Народ кто нибудь может растянуть пример 7, пунктов на 50? а то пробую то ССЫЛКА выскакивает то ЗНАЧ
Аноним, 15 октября 2017 г.
[id295933596|Михаил], если не сложно растяните 7 пример пунктов на 50, а то пробую то ССЫЛКА выскакивает то ЗНАЧ
Михаил, 16 октября 2017 г.
изменил формулу и перезалил файл примера. там показано как добавлять строки
Аноним, 12 ноября 2017 г.
Подскажите , как выводить в диапазоне двух дат (2 столбца) "С" и "До" , например акция будет доступна с 12 ноября по 31 декабря, тоже самое как здесь выводить (до / после / включая / не включая) = выбрать одну дату =список ( достуные акции - например на сегодня )
Михаил, 18 ноября 2017 г.
Отличный вопрос. Я добавил в статью и файл примера новую задачу 5а с решением вашей задачи.
Аноним, 15 февраля 2018 г.
Доброе утро! Помогите плиз! Спасибо за великолепные примеры. Подскажите, как в 9 примере (там где можно выбирать несколько параметров одновременно) можно вставить дополнительные строки в массив расчёта значений больше (1000). При добавлении строк с 11 по 17 либо не считает, либо пишет, что нельзя изменять массив. Сделать добавление как в примере #7 так же не получилось.Очень жду ответа!Спасибо!
Михаил, 15 февраля 2018 г.
Добрый вечер, я изменил формулу массива. Теперь она возвращает только 1 значение. В файле примера я добавил пояснение как увеличить количество строк исходной таблице. При 1000 строк Excel может начать тормозить из-за формул массива.
Аноним, 23 февраля 2018 г.
Статья великолепная! Очень жаль, что у меня ничего не получается :с По вашему примеру пыталась сделать, но ничего не получилось((( Выдает #ЗНАЧ Пример 4, там где диапазон дат. Помогите пожалуйста
Михаил, 24 февраля 2018 г.
Спасибо. Для ввода формул массива не забывайте нажимать сочетания клавиш CTRL+SHIFT+ENTER
Аноним, 11 декабря 2018 г.
Судя по дате статьи, полагаю ответа я не дождусь, но все же попробую. Здравствуйте, подскажите как сделать так, чтобы запрос происходил и с учетом диапазона цен, диапазона дат, и плюс товар и месяц. Как не пытался разобрать код, не получается
Аноним, 11 декабря 2018 г.
А нет, все, разобрался, спасибо за статью, вы просто не представляете как сильно мне помогли!
Михаил, 12 декабря 2018 г.
Я рад, что пригодилось. Отвечаем вне зависимости от даты статьи))
Аноним, 30 марта 2019 г.
Выбрал данные из таблицы по 3 примеру, всё отлично, но в пустых строчках #ЧИСЛО! а хотелось просто пустые ячейки. В чём дело...?
Аноним, 30 марта 2019 г.
Использовал: =ИНДЕКС(A3:A355;НАИМЕНЬШИЙ(ЕСЛИ($R$6=B3:B355;СТРОКА(B3:B355);"");СТРОКА(B3:B355)-СТРОКА($B$2))-СТРОКА($B$2))
Аноним, 30 марта 2019 г.
Сразу не увидел, использовал условное форматирование и ничего не видно. СПАСИБО.😃
Аноним, 3 февраля 2020 г.
Добрый день! Не надеюсь на ответ, но помощь очень нужна.. Пример 4, решение 2, задание идентично. Но вот функция возвращает исключительно одно значение, а не диапазон(( Формула там точно верная? Что может быть не так? [id295933596|Михаил Петров]
Михаил, 4 февраля 2020 г.
Здравствуйте, формула работает правильно ))) Скачайте файл примера (синяя кнопка внизу статьи) и вставьте свои значения. Возможно, вы вводите формулу не как формулу массива, а как простую формулу. Вместо ENTER нажимайте CTRL+SHIFT+ENTER
Аноним, 16 февраля 2020 г.
Добрый день! Возможен ли вывод таблицы на другом листе. И возможно ли добавлять строки в исходную таблицу, когда уже создана функция вывода? Не разрешает раздвигать строки.
Михаил, 23 февраля 2020 г.
Если Вы выводите таблицу запроса формулами, то ее, естественно, можно расположить на любом листе или в другой книге. Конечно, строки добавлять можно, но для этого нужно разобраться с формулами массива. Материал данной статьи сложный из-за формул массива.
Аноним, 28 апреля 2020 г.
Доброго времени суток! Спасибо за полезную статью! помогите пожалуйста, ситуация в следующем: Есть таблица с искомыми данными: Вид работ/период работ/наименование техники / номенклатурный номер/наименование з.ч / кол-во. Благодаря вашей статье смог на отдельном листе отобразить данные по 3 критериям (период начало, конец и вид работ), но т.к. в условно выбранный период и при выбранном виде работ по разной технике есть дублирующиеся позиции запасных частей, в таблице данные позиции отображаются 2 и более раз (значения не уникальны), как доработать формулу чтобы она выявляла дублирующиеся позиции и отображала только 1 раз или отображала только уникальные значения и только 1 раз. =ЕСЛИОШИБКА(ИНДЕКС(Массив $F$5:$F$4298 ; НАИМЕНЬШИЙ (ЕСЛИ((дата начало - $D$2<= диапазон дат - $E$5:$E$4298) * ($D$3>=Диапазон дат $E$5:$E$4298)*(вид работ $D$4= диапазон видов работ $B$5:$B$4298);СТРОКА($E$5:$E$4298);"");СТРОКА($E$5:$E$4298)-СТРОКА($E$4))-СТРОКА($E$4));"")
Михаил, 29 апреля 2020 г.
добрый день, вопрос понятен. можете вопрос прислать в нашу группу https://vk.com/excel2ru там есть возможность прицепить файл. сразу сделаю в вашем файле. Только данные удалите лишние (у вас в формуле 4298 строк!), мне 10 строк будет достаточно и не шлите лишние столбцы-не участвующие в расчетах, они только путают.
Аноним, 7 июля 2020 г.
Здравствуйте, подскажите, как в в диапазоне данных, представленных в разнобой, найти. То есть, в списке значений от 1 до 1000, представленных по группам и вразброс (не по порядку), найти конкретные номера в диапазоне от 105 до 190 и выделить эти ячейки цветом или проставить в отдельном столбце напротив данных ячеек значение, например 1. Сортирова при этом не подходит, так как необходимо монимать в каких группах эти значения есть.? Благодарю
Аноним, 2 сентября 2020 г.
Здравствуйте! Каким образом можно из таблицы "товар, магазин, дата, цена", где много повторяющихся значений, получить таблицу с последними актуальными ценами на товары?
Михаил, 2 сентября 2020 г.
хороший вопрос 1) сначала сформируем перечень товаров без повторов 2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата]) 3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8) Изменил файл примера, добавил лист с задачей №12 Актуальная цена
Евгений, 27 ноября 2020 г.
Благодарю за отличную статью, только пожалуйста подскажите, а как сделать так, в примере 9, чтобы искало не точные текстовое значения товаров, а только часть значения. Например если названия товаров были бы из 5 слов, и надо было бы отбирать только те, где встречаются 2 определенных слова из этих 5, стоящих подряд.
Михаил, 27 ноября 2020 г.
Марк, 5 марта 2021 г.
Спасибо за отличную статью. Может кто нибудь подсказать в каком направлении двигаться, если у меня значения в течение недели переходят в разные статусы (принято в ремонт, выполнено, оплачено), как сделать выборку в пятницу по состоянию "принято в ремонт", если некоторые из них уже перешли в состояние "выполнено"? В отчете директор хочет видеть сколько я принял и сколько выполнено.
Михаил, 7 марта 2021 г.
для каждого изменения статуса нужно делать отдельную запись: сначала строка со статусом "принято в ремонт", затем новая строка (будет дублирование информации) с новым статусом "выполнено" и тд. Тогда можно будет отфильтровать все заказы, у которых в течение недели был статус "принято в ремонт".
Igor, 2 апреля 2021 г.
Добрый день! Подскажите пожалуйста, есть база данных на 30000 клиентов в excel. Нужно сделать сортировку и удалить все неподходящие позиции. Условие: в ячейке должно содержаться определенное слово, скажем "Microsoft", т.е. сортировка не по значению во всей ячейке, а только по одному слову. Возможно ли это? Спасибо большое :)
Михаил, 2 апреля 2021 г.
Здравствуйте. Воспользуйтесь инструментом автофильтр, см. статью https://excel2.ru/articles/avtofiltr-v-ms-excel В разделе Отбор по столбцу с текстовыми значениями этой статьи показано как задать условие
Вячеслав, 26 мая 2021 г.
Здравствуйте, подскажите пожалуйста, есть таблица в которой имеются группы(подразделения), ниже которых записаны данные в большом количестве. Так вот, как можно вначале отфильтровать по группам(подразделениям) выбрать конкретное, а не из всей базы, затем по критерию данных этого подразделения вывести все нужные строки которые содержат нужный критерий определенного столбца таблицы. Для наглядности поясню: есть три подразделения, бригада 1, бригада 2 и бригада 3. Каждое из них имеет определенные автомобили например Урал, КамАЗ, раз и ТД и та и не в единичном количестве. И допустим ещё один столбец по которому так же необходим вывод данных, назовем его группой, она в себя включает: 1, 2, 3. Остальные данные таблицы озвучивать не вижу смысла, ибо по вашей статье они и так будут выводиться согласно выбранным критериям и будут соответствовать им. Спасибо большое, жду вашего ответа
Михаил, 31 мая 2021 г.
К сожалению, не смог понять ваше объяснение. Лучше пишите в группу https://vk.com/excel2ru и прикладывайте файл.
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved