Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Вывести данные по условию (ArrayFormula) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вывести данные по условию (ArrayFormula) (Формулы/Formulas)
Вывести данные по условию (ArrayFormula)
Faraway Дата: Четверг, 28.10.2021, 14:36 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Здравствуйте.
У меня есть список позиций, в котором описано название, описание и другие данные.
Когда я делаю выборку с данного списка, то я вношу название позиции, а остальные данные сами подтягиваются. Использую для этого я функцию FILTER. Но мне приходится в каждой ячейке вставлять формулу фильтра. А как такого же результата можно добиться через ArrayFormula? FILTER с ArrayFormula не дружит.
Таблица, с демонстрацией В табличке в столбиках ABC то, как оно работает сейчас у меня, А в EFG можно подсказать другое решение.
Спасибо.
 
Ответить
СообщениеЗдравствуйте.
У меня есть список позиций, в котором описано название, описание и другие данные.
Когда я делаю выборку с данного списка, то я вношу название позиции, а остальные данные сами подтягиваются. Использую для этого я функцию FILTER. Но мне приходится в каждой ячейке вставлять формулу фильтра. А как такого же результата можно добиться через ArrayFormula? FILTER с ArrayFormula не дружит.
Таблица, с демонстрацией В табличке в столбиках ABC то, как оно работает сейчас у меня, А в EFG можно подсказать другое решение.
Спасибо.

Автор - Faraway
Дата добавления - 28.10.2021 в 14:36
Gustav Дата: Четверг, 28.10.2021, 15:46 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1952
Репутация: 784 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А зачем в данном случае FILTER? Такие штуки обычно через ВПР делаются (VLOOKUP). Формула для ячейки B3:
[vba]
Код
=ArrayFormula(VLOOKUP(A3:INDEX(A:A;MAX(1; ROW(A3:A100)*(A3:A100<>"")));J3:L17;{2\3};FALSE))
[/vba]Это с динамическим расчетом занятой области в диапазоне A1:A100 (чтобы #N/A не появлялись).

А если присутствие #N/A допустимо (типа "глаза не мозолят"), то формула значительно проще (тоже для B3):
[vba]
Код
=ArrayFormula(VLOOKUP(A3:A100;J3:L17;{2\3};FALSE))
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Четверг, 28.10.2021, 15:49
 
Ответить
СообщениеА зачем в данном случае FILTER? Такие штуки обычно через ВПР делаются (VLOOKUP). Формула для ячейки B3:
[vba]
Код
=ArrayFormula(VLOOKUP(A3:INDEX(A:A;MAX(1; ROW(A3:A100)*(A3:A100<>"")));J3:L17;{2\3};FALSE))
[/vba]Это с динамическим расчетом занятой области в диапазоне A1:A100 (чтобы #N/A не появлялись).

А если присутствие #N/A допустимо (типа "глаза не мозолят"), то формула значительно проще (тоже для B3):
[vba]
Код
=ArrayFormula(VLOOKUP(A3:A100;J3:L17;{2\3};FALSE))
[/vba]

Автор - Gustav
Дата добавления - 28.10.2021 в 15:46
Faraway Дата: Пятница, 29.10.2021, 14:37 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Gustav, спасибо, с такой функцией я не знаком был.
Переделал формулу к такому виду:
Код
=ArrayFormula(ЕСЛИ(A3:A100="";"";ВПР(A3:A100;G3:I17;{2\3};ЛОЖЬ)))

Еще момент, как я понимаю, то функция работает только если сравнение идет по первому столбику, а как быть, если ситуация обратная? т.е. надо также данные, которые в первом столбике получить исходя из того, что во втором столбике. Обновил табличку. Неверное сразу стоило сделать полный пример, не сообразил, что некоторые функции работают только в одну сторону.
Посмотрите еще раз пожалуйста, есть ли решение и для того, чтоб заполнить и первый столбец, туда можно другую формулу, главное чтоб все формулы были в верхней строке.
 
Ответить
СообщениеGustav, спасибо, с такой функцией я не знаком был.
Переделал формулу к такому виду:
Код
=ArrayFormula(ЕСЛИ(A3:A100="";"";ВПР(A3:A100;G3:I17;{2\3};ЛОЖЬ)))

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

Автор - Faraway
Дата добавления - 29.10.2021 в 14:37
Kashimirush Дата: Пятница, 29.10.2021, 14:39 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 37 ±
Замечаний: 0% ±

Excel 2010
Faraway, В качестве диапазона данных VLOOKUP можно задать массив, например если вам нужно искать колонку А по значениям из колонки В, то формулу стоит записать так:


Работа, работа, перейди на Федота...
 
Ответить
СообщениеFaraway, В качестве диапазона данных VLOOKUP можно задать массив, например если вам нужно искать колонку А по значениям из колонки В, то формулу стоит записать так:

Автор - Kashimirush
Дата добавления - 29.10.2021 в 14:39
Kashimirush Дата: Пятница, 29.10.2021, 14:39 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 37 ±
Замечаний: 0% ±

Excel 2010
Faraway, В качестве диапазона данных VLOOKUP можно задать массив, например если вам нужно искать колонку А по значениям из колонки В, то формулу стоит записать так:

Код
VLOOKUP( C1; {B1:B100\А1:A100} ; 2; 0 )
;

Где запись вида {B1:B100\А1:A100} - позволяет создавать массивы, если между елементами стоит знак "\" - значит мы вставляем следующий элемент в следующую колонку, если между элементами стоит ";" - то следующий элемент мы ставим после первого, на следующую строку.


Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Пятница, 29.10.2021, 14:43
 
Ответить
СообщениеFaraway, В качестве диапазона данных VLOOKUP можно задать массив, например если вам нужно искать колонку А по значениям из колонки В, то формулу стоит записать так:

Код
VLOOKUP( C1; {B1:B100\А1:A100} ; 2; 0 )
;

Где запись вида {B1:B100\А1:A100} - позволяет создавать массивы, если между елементами стоит знак "\" - значит мы вставляем следующий элемент в следующую колонку, если между элементами стоит ";" - то следующий элемент мы ставим после первого, на следующую строку.

Автор - Kashimirush
Дата добавления - 29.10.2021 в 14:39
Faraway Дата: Пятница, 29.10.2021, 15:27 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Kashimirush, я пошел изучать функцию, как что-то пойму - вернуть на форум, а то что то я не могу понять "\" и ";".
Спасибо.
 
Ответить
СообщениеKashimirush, я пошел изучать функцию, как что-то пойму - вернуть на форум, а то что то я не могу понять "\" и ";".
Спасибо.

Автор - Faraway
Дата добавления - 29.10.2021 в 15:27
Faraway Дата: Воскресенье, 14.11.2021, 13:05 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Пересмотрел много примеров использования и "уроков" по функции ВПР. Ни в одном уроке (по крайней мере в тех что я нашел) не показано как использовать ВПР вместе с ArrayFormula и как управлять массивами.
По этому я решил просто всмотреться в формулу и попробовать понять ее логику составления + смотреть ваши объяснения.
Правильно ли я понимаю, что если я в фигурных скобках "{}" буду указывать столбики либо диапазоны через символ "\" , то они будут работать в том порядке, в котором я его указываю.
Например ВПР ищет по первому столбику, но если мне надо найти значение из столба G исходя из значений в столбе H, то мне надо указать их в обратном порядке, а именно {H3:H17\G3:G17} (учитывая также конкретный диапазон, а не всю длину столбов).
отсюда формула, которая мне нужна будет такого вида:
Код
ВПР(B3:B100;{H3:H17\G3:G17};2;0)

Т.е. я указываю диапазоны, при этом первым я указываю тот по которому ищем, а далее через "\" указываем где ищем. Правильно я все понял?
Также можно в фигурных скобках "{}" указывать номера столбцов для вывода через символ "\", и указывать можно те столбики которые мне нужны + в таком порядке как я хочу, верно?
А вот про использование ";" в массиве я пока не понял. Но еще повникаю и поэкспериментирую.


Сообщение отредактировал Faraway - Воскресенье, 14.11.2021, 13:07
 
Ответить
СообщениеПересмотрел много примеров использования и "уроков" по функции ВПР. Ни в одном уроке (по крайней мере в тех что я нашел) не показано как использовать ВПР вместе с ArrayFormula и как управлять массивами.
По этому я решил просто всмотреться в формулу и попробовать понять ее логику составления + смотреть ваши объяснения.
Правильно ли я понимаю, что если я в фигурных скобках "{}" буду указывать столбики либо диапазоны через символ "\" , то они будут работать в том порядке, в котором я его указываю.
Например ВПР ищет по первому столбику, но если мне надо найти значение из столба G исходя из значений в столбе H, то мне надо указать их в обратном порядке, а именно {H3:H17\G3:G17} (учитывая также конкретный диапазон, а не всю длину столбов).
отсюда формула, которая мне нужна будет такого вида:
Код
ВПР(B3:B100;{H3:H17\G3:G17};2;0)

Т.е. я указываю диапазоны, при этом первым я указываю тот по которому ищем, а далее через "\" указываем где ищем. Правильно я все понял?
Также можно в фигурных скобках "{}" указывать номера столбцов для вывода через символ "\", и указывать можно те столбики которые мне нужны + в таком порядке как я хочу, верно?
А вот про использование ";" в массиве я пока не понял. Но еще повникаю и поэкспериментирую.

Автор - Faraway
Дата добавления - 14.11.2021 в 13:05
Gustav Дата: Воскресенье, 14.11.2021, 14:45 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1952
Репутация: 784 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А вот про использование ";" в массиве я пока не понял. Но еще повникаю и поэкспериментирую.

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


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
А вот про использование ";" в массиве я пока не понял. Но еще повникаю и поэкспериментирую.

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

Автор - Gustav
Дата добавления - 14.11.2021 в 14:45
Faraway Дата: Воскресенье, 14.11.2021, 17:37 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

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

Правильно ли я понял, что используя ";" я могу объединять диапазоны в таком виде:
Код
{A3:F8;A15:F18;B5:G33}
?
Если да, тогда почему не получается заменить
Код
ВПР(B3:B100;{H3:H17\G3:G17};2;0)
на
Код
ВПР(B3:B100;{H3:H17;G3:G17};2;0)
?


Сообщение отредактировал Faraway - Воскресенье, 14.11.2021, 17:41
 
Ответить
Сообщение
Точка с запятой соединяет строки массивов (диапазонов), образуя единый вертикальный массив.

Правильно ли я понял, что используя ";" я могу объединять диапазоны в таком виде:
Код
{A3:F8;A15:F18;B5:G33}
?
Если да, тогда почему не получается заменить
Код
ВПР(B3:B100;{H3:H17\G3:G17};2;0)
на
Код
ВПР(B3:B100;{H3:H17;G3:G17};2;0)
?

Автор - Faraway
Дата добавления - 14.11.2021 в 17:37
Gustav Дата: Воскресенье, 14.11.2021, 19:21 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1952
Репутация: 784 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
ВПР(B3:B100;{H3:H17\G3:G17};2;0)
Здесь у вас поиск идёт по массиву из 15 строк х 2 колонки.
ВПР(B3:B100;{H3:H17;G3:G17};2;0)
А здесь - из 30 строк х 1 колонку. Чувствуете разницу?

Введите где-нибудь в свободной части листа формулы и посмотрите как выглядят эти массивы:
[vba]
Код
={H3:H17\G3:G17}
={H3:H17;G3:G17}
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 14.11.2021, 19:25
 
Ответить
Сообщение
ВПР(B3:B100;{H3:H17\G3:G17};2;0)
Здесь у вас поиск идёт по массиву из 15 строк х 2 колонки.
ВПР(B3:B100;{H3:H17;G3:G17};2;0)
А здесь - из 30 строк х 1 колонку. Чувствуете разницу?

Введите где-нибудь в свободной части листа формулы и посмотрите как выглядят эти массивы:
[vba]
Код
={H3:H17\G3:G17}
={H3:H17;G3:G17}
[/vba]

Автор - Gustav
Дата добавления - 14.11.2021 в 19:21
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вывести данные по условию (ArrayFormula) (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!