Выборка из массива данных по запросу

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выборка из массива данных по запросу (Макросы/Sub)

Gopronotmore

Дата: Понедельник, 29.06.2015, 16:42 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 199

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

Добрый день уважаемые форумчане!

У меня к Вам вопрос, покажите дилетанту как быть в этой ситуации!

Есть файл в нем 2 листа, в первом массив данных во втором выборка из массива

Сделал через IF но это только для 4 запросов, а если их будет 100 ? Программа запретит писать такую большую формулу!

Задача: Как сделать так, что бы при выборе определенной позиции данные брались из массива который в Input Data. Через VLOOKUP не получается сделать, потому что он берет данные по первому совпадению.

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

К сообщению приложен файл: 3549417.xlsx (9.0 Kb)

Сообщение отредактировал Gopronotmore - Понедельник, 29.06.2015, 16:57

Ответить

Manyasha

Дата: Понедельник, 29.06.2015, 16:56 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 898 ±

Замечаний: 0% ±

Excel 2010, 2016

Gopronotmore, здравствуйте, Вам обязательно макрос нужен?
Посмотрите формульный вариант:

Код

=ИНДЕКС('Input Data'!C$2:C$30;НАИМЕНЬШИЙ(ЕСЛИ('Input Data'!$B$2:$B$30=Form!$B$2;СТРОКА('Input Data'!B$2:B$30)-1;"");СТРОКА(A1)))

формула массива, вводится сочетанием ctrl+shift+enter

К сообщению приложен файл: 3549417_1.xlsx (9.1 Kb)

ЯД: 410013299366744 WM: R193491431804

Ответить

Gopronotmore

Дата: Понедельник, 29.06.2015, 17:02 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 199

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

Большое спасибо! Мне не критично! Буду пытаться адаптировать Вашу формулу к своему файлу! Тему не закрывайте, если будут вопросы, можно будет тут отписаться!

Спасибо!

Ответить

Gopronotmore

Дата: Вторник, 30.06.2015, 09:38 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 199

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

А можете расписать как эта формула работает ? Разобрался, и применил, но вот смысла не понимаю! Напишите если не трудно в чем принцип работы ? Понимаю что берет данные из массива, но не могу понять для чего ROW и SMALL как они оказывают влияние на выводимый результат? Спасибо!

Сообщение отредактировал Gopronotmore - Вторник, 30.06.2015, 11:31

Ответить

Manyasha

Дата: Вторник, 30.06.2015, 12:39 | Сообщение № 5

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 898 ±

Замечаний: 0% ±

Excel 2010, 2016

Gopronotmore,

Код

IF('Input Data'!$B$2:$B$30=Form!$B$2,ROW('Input Data'!B$2:B$30),"")

возвращает массив, состоящий из номеров строк, для которых названия машин равны названию В2 на листе Form, остальные элементы массива приравниваются к "" (пустая строка). Т.е для "машина2" массив будет

Код

{"", "", "", 2, 3, 4, "", ""..., ""}

Такая нумерация не подходит для нашей функции INDEX(), т.к. 1-й элемент массива 'Input Data'!C$2:C$30 расположен на 2-й строке листа (функция Row возвращает номер строки именно на листе). Отнимем 1 от возвращаемого номера строки, чтобы полученный номер совпадал с номером элемента массива, для "машина2" массив будет

Код

{"", "", "", 3, 4, 5, "", ""..., ""}

Данная формула НЕ протягиваемая, если Вы запишите ее в одну ячейку и будете тянуть вниз, возвращаться у Вас будет все время первый элемент массива ("").
Для того чтобы формула возвращала элементы со сдвигом и игнорировала элементы = "", используем функцию SMALL(<наш полученный массив>, k) - посмотрите в описании, как она работает, если не понятно.
Для первой ячейки, где мы записали формулу k=ROW(A1) = 1. Т.к. ссылка относительная, k будет меняться при протягивании, т.е. во второй ячейке Вы увидите уже ROW(A2) = 2.
Итак, после применения функции SMALL наш массив уже будет таким:

Код

{3, 4, 5, "", ""..., ""}

Функцию INDEX() тоже посмотрите в описании, она работает уже с последним массивом.

Надеюсь понятно объяснила, кстати, посмотрите еще Как разобраться в работе сложной формулы?

Gopronotmore,

Код

IF('Input Data'!$B$2:$B$30=Form!$B$2,ROW('Input Data'!B$2:B$30),"")

Код

{"", "", "", 2, 3, 4, "", ""..., ""}

Код

{"", "", "", 3, 4, 5, "", ""..., ""}

Код

{3, 4, 5, "", ""..., ""}

ЯД: 410013299366744 WM: R193491431804

Ответить

СообщениеGopronotmore,

Код

IF('Input Data'!$B$2:$B$30=Form!$B$2,ROW('Input Data'!B$2:B$30),"")

Код

{"", "", "", 2, 3, 4, "", ""..., ""}

Код

{"", "", "", 3, 4, 5, "", ""..., ""}

Код

{3, 4, 5, "", ""..., ""}

Автор - Manyasha
Дата добавления - 30.06.2015 в 12:39

Gopronotmore

Дата: Вторник, 30.06.2015, 12:49 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 199

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

Все спасибо за полный ответ! Теперь принцип работы понятен!

Ответить

ABVS

Дата: Четверг, 14.01.2016, 08:47 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 1

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Подскажите как реализовать ?

Здравствуйте в написание кода я не силен, только начал осваивать....

подскажите как реализовать

Есть БД в Excel

в первом столбце есть номер закупки второй и третий пустые. ( но есть документ на бумаге где у номера закупки есть свой порядковый номер например закуп 43 порядковый номер 1 44 порядковый номер 2, 46 порядковый номер 3 и т.д. Эти номера закупок повторяются в разбросе по всему первому столбцу) всего скажем номеров закупок 120 У каждой закупки свой порядковый номер - хочу забить порядковый номер закупки в третий столбец).

Во втором столбце сделать выбор например проверить первый столбец на закуп 43, 44, 46 и тд если нашел значения 43, 44, 46 то во втором столбце напротив тех значений ввести порядковые номера соответствующие закупу 1,2,3

С чего начать ??
подскажите

Ответить

anvg

Дата: Четверг, 14.01.2016, 08:57 | Сообщение № 8

Группа: Друзья

Ранг: Ветеран

Сообщений: 581

Репутация: 271 ±

Замечаний: 0% ±

2016, 365

Доброе время суток
Добро пожаловать на форум!
С чего начать ?? - Правила форума (Обязательно прочесть перед созданием тем!)
[moder]Всё верно) А эта тема закрыта[/moder]

Сообщение отредактировал Pelena - Четверг, 14.01.2016, 08:59

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выборка из массива данных по запросу (Макросы/Sub)

Страница 1 из 1
1