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

Дата: Среда, 14.01.2015, 11:34 | Сообщение № 1

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

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

Сообщений: 6

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

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

Excel 2013

Привет
Прошу помощи
Делаю в бюджете категории "боли" - расходы, с которыми будем прощаться при плохих продажах.
В строках указаны расходы и сумма по месяцам. В первой колонке - категория боли - насколько просто будет нам не тратить эти деньги.
На другом листе, хочу собрать такие расходы в таблицы по категориям. Т.е. сначала все расходы категории боли1, потом боли2, потом боли 3.
ВПР не позволяет, ибо находит первое значение и его ставит, на следующее не переходит. Перебрал вроде формул, но не решает проблему.
Как быть, подскажите!

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

Дата: Среда, 14.01.2015, 11:46 | Сообщение № 2

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

Ранг: Участник клуба

Сообщений: 2941

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

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

Естественно. ВПР ищет первое вхождение.
Варианты.
1. Есть решение ВПР2, реализованное в VBA.
2. Можно составить формулу массива, но при большом количестве таких формул возможно торможение при пересчетах.
3. Проще: доп. столбец с подсчетом значений "боль1", "боль2"... в столбце А:
СЧЕТЕСЛИ($A$1:A2;A2)&A2
Далее: или ВПР с поиском сцепленных данных (если доп. столбец вставить первым, в этом случае ссылки будут на столбец В), или ИНДЕКС/ПОИСКПОЗ

Сообщение отредактировал vikttur - Среда, 14.01.2015, 11:49

Дата: Среда, 14.01.2015, 11:54 | Сообщение № 3

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

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

Сообщений: 6

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

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

Excel 2013

Цитата vikttur, 14.01.2015 в 11:46, в сообщении № 2

3. Проще: доп. столбец с подсчетом значений "боль1", "боль2"... в столбце А:
СЧЕТЕСЛИ($A$1:A2;A2)&A2
Далее: или ВПР с поиском сцепленных данных (если доп. столбец вставить первым, в этом случае ссылки будут на столбец В), или ИНДЕКС/ПОИСКПОЗ

Спасибо! Но пока не понял. Собрал формулу СЧЕТЕСЛИ, посчиталось количество болей в каждой категории - http://take.ms/bGk9A
Но эти значения висят каждый в своей ячейке. А дальше как? Не понимаю...

TimSha

Дата: Среда, 14.01.2015, 11:59 | Сообщение № 4

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

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

Сообщений: 627

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

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

Excel 2013 Pro +

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

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

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

Сообщение отредактировал TimSha - Среда, 14.01.2015, 12:05

Дата: Среда, 14.01.2015, 11:59 | Сообщение № 5

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

Ранг: Участник клуба

Сообщений: 2941

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

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

Пример. В ячейке С2 выбор боли (мазохизм? :))

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

Дата: Среда, 14.01.2015, 12:14 | Сообщение № 6

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

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

Сообщений: 6

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

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

Excel 2013

Цитата TimSha, 14.01.2015 в 11:59, в сообщении № 4

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

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

Дата: Среда, 14.01.2015, 12:18 | Сообщение № 7

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

Ранг: Участник клуба

Сообщений: 2941

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

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

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

P.S. написанное в этом сообщении относится к моему решению.

Сообщение отредактировал vikttur - Среда, 14.01.2015, 12:24

Дата: Среда, 14.01.2015, 12:20 | Сообщение № 8

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

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

Сообщений: 6

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

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

Excel 2013

Цитата vikttur, 14.01.2015 в 11:59, в сообщении № 5

Пример. В ячейке С2 выбор боли (мазохизм? :))

Ого там формула! Спасибо огромное! То, что нужно. Правда прочесть формулу и расшифровать ее у меня не получается )
А как сделать, чтобы не в 1 таблице, в разных таблицах были разные боли, чтобы как раз все было видно на одном листе, без переключений.

Дата: Среда, 14.01.2015, 12:24 | Сообщение № 9

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

Ранг: Участник клуба

Сообщений: 2941

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

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

См. сообщение №7

Формула простая. Искомое значение:

Код

СТРОКА(A3)-ПОИСКПОЗ($C$2;$C$1:$C2;)&$C$2

СТРОКА(A3)-ПОИСКПОЗ($C$2;$C$1:$C2;)- разница строк между текущей и строкой с заглавием таблицы ("боль1")
Для первой строки таблицы будет 1, для второй - 2...
Т.е.
ВПР(1&"боль1";диапазон;в_каком_столбце;)
ВПР(2&"боль1";диапазон;в_каком_столбце;)

Сообщение отредактировал vikttur - Среда, 14.01.2015, 12:29

TimSha

Дата: Среда, 14.01.2015, 12:35 | Сообщение № 10

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

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

Сообщений: 627

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

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

Excel 2013 Pro +

Поправил файл - дополнил, фильтр по "боли" - без пустых.

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

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

Сообщение отредактировал TimSha - Среда, 14.01.2015, 12:36

Дата: Среда, 14.01.2015, 12:43 | Сообщение № 11

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

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

Сообщений: 6

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

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

Excel 2013

Цитата TimSha, 14.01.2015 в 12:35, в сообщении № 10

Поправил файл - дополнил, фильтр по "боли" - без пустых.
К сообщению приложен файл: ZXC_2_need_help.xlsx(20Kb)

И снова нет. Спасибо, vikttur уже решил мой вопрос. Посмотрите, ради интереса. Там адова формула.

Дата: Среда, 14.01.2015, 12:45 | Сообщение № 12

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

Ранг: Участник клуба

Сообщений: 2941

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

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

[offtop]

Цитата

Там адова формула.

Простая до "немогу". Вы адовых не видели :)[/offtop]

Дата: Среда, 14.01.2015, 12:45 | Сообщение № 13

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

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

Сообщений: 6

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

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

Excel 2013

Цитата vikttur, 14.01.2015 в 12:24, в сообщении № 9

Формула простая. Искомое значение:

СТРОКА(A3)-ПОИСКПОЗ($C$2;$C$1:$C2;)&$C$2

СТРОКА(A3)-ПОИСКПОЗ($C$2;$C$1:$C2;)- разница строк между текущей и строкой с заглавием таблицы ("боль1")
Для первой строки таблицы будет 1, для второй - 2...
Т.е.
ВПР(1&"боль1";диапазон;в_каком_столбце;)
ВПР(2&"боль1";диапазон;в_каком_столбце;)

Все равно, не понимаю, как она работает )))
Но она работает. По таблицам размножил.
Еще раз, огромное спасибо!

Дата: Среда, 14.01.2015, 12:54 | Сообщение № 14

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

Ранг: Участник клуба

Сообщений: 2941

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

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

Добавьте к этой часли формулы "=". Получится отдельная формула.
Gротяните ее по столбцу вниз. Посмотрите на результат.

Еще вариант для изучения работы формул:
в строке формул выделить часть формулы и нажать F9 (ручной пересчет). Увидите результат вычисления выделенной части.
Вернуться назад - стрелка возврата или Esc