Как стать автором
Обновить

Комментарии 14

Осталось только нанести на график количество печенек изо дня в день и прочертить линию тренда
Ну, все-таки графики — это несколько отдельная часть экселя, да, и раздувать мануал не хотелось)
При описании функции ВПР, Вы рассмотрели ситуацию, когда таблицы не идентичны: Таблица с результатом:(Продукт|Январь-Апрель) и Справочник:(Продукт|Январь-Май). Но схитрили и опустили значительную часть фокуса. Результат и функция годится только для ячейки B3. При попытке растянуть как по горизонтали, так и по вертикали формула сбивается. Я использовал для этих целей именованные диапазоны. Создавать их не трудно. Перед тем как использовать формулу, нужно выделить наш диапазон H3:M6 и в поле, расположенном слева от формулы, назначить любое имя. Например, написать СПР_1 и нажать Enter. Для имени диапазона существуют ограничения: нельзя чтобы они совпадали с именованием какой-либо ячейки(недопустимо Y10, т.к. это будет не диапазон, а ссылка на ячейку), двух диапазонов с одинаковым именем тоже не может быть. Таким образом полноценная формула для Вашего примера приобретает красивый вид:
=ВПР($A3; СПР_1; СТОЛБЕЦ();0)

Но и это не всё. Если теперь в справочнике перемешать название месяцев, допустим, поместить их в обратном порядке Май-Январь функция СТОЛБЕЦ() становится бессильной. И да, придётся вручную прописывать номер столбца цифрой, предварительно сопоставив их.
То есть я бы такие примеры представлял по-иному.
Представим ситуацию, что у нас есть справочная таблица (Продукты|Январь|Февраль|...|Декабрь) и нам необходимо вывести в результирующую таблицу произвольный квартал.
Решение: создаём диапазон включающий всю справочную таблицу(без заголовков, естественно), назовём его СПР_ГОД.
Теперь исходя из потребностей можно вывести любой квартал в таблицу вида(Продукты|Месяц_1|Месяц_2|Месяц_3).
Третий квартал почти закончился, вот его и возьмём, представим что мы завтра должны составить отчёт по нему:
Создаём результирующую таблицу (Продукты|Июль|Август|Сентябрь).
Теперь формула для B3 должна выглядеть так:
=ВПР($A3; СПР_ГОД; 8;0)
Для C3:
=ВПР($A3; СПР_ГОД; 9;0)
Для D3:
=ВПР($A3; СПР_ГОД; 10;0)

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

Всё это в своё время делалось в Excel 2007, возможно в новых версиях, что-то изменилось.
P.S. Спасибо за статью, она в любом случае будет полезна для интересующихся.
P.P.S. И это… ))) Простите, но у Вас в первом примере реализация и условие прямо противоположны.
Одним словом: #define TRUE FALSE // упс
Добрый день!

Отвечаю на Ваши комментарии:
по первому абзацу — скажите, а Вы использовали закрепление ячеек? Описанная Вами ситуация актуальна только для формулы вида
=ВПР($A3;H3:M6; СТОЛБЕЦ();0)

когда аргумент «таблица» — диапазон H3:M6 записывается без закрепления ячеек. Если же сделать запись с учетом закрепления ячеек — $H$3:$M$6, то вне зависимости от направления (кроме движения влево), формула будет актуальна для таблицы с результатом.

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

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

Сам сталкивался с чем то похожим, но уже от пользователей. Месяцы вперемежку — за гранью добра и зла, конечно…
Челлендж на ачивку «опытный пользователь»: как при открытии файла обновлять ссылки на другие книги Excel? Мои пользователи меняют цены в одном файле, на который ссылаются другие, закрывают файл. Открывают те документы, в котрых имеются ссылки — а цены там старые. (Офис 2003, в других пакетах не проверял)
UPD. Если открыты оба этих файла, то проблем нет.
Актуально для excel 2007 и далее:
Верхняя вкладка «Данные — > Изменить связи», там уже необходимо выбрать нужный файл и нажать «Обновить». Кроме того связи там можно изменить или вообще оборвать
ОК, спасибо. Придется переезжать на новый офис.
Увы, выяснилось, этот способ не работает, если в файле, куда ссылаются другие книги, добавить/удалить строки/стобцы (что частенько бывает), т.е. изменить координаты ссылаемой ячейки. Ну это естественно, откуда Excel знает, что какая-то другая ячейка оказалась вместо искомой. Однако же, при открытых файлах можно свободно добавлять/удалять, система сама обновляет ссылки.
При открытых файлах изменение происходит динамически, при закрытых, конечно, если внести изменения в саму структуру, то изменения не отобразятся
Такой способ не пробовали: «Установка автоматического обновления связи с другой программой»?
Или можно поробовать сделать автозапускаемый при открытии макрос: Автоматический запуск макроса во время открытия книги, Автозапуск макроса в Excel при открытии файла, а в нем уже автообновлять: как-то так.
Часто бывает что значения с виду одинаковые, но некоторые поставщики любят подложить каку и сделать невозможным точное сравнение — в артикулах заменить английские буквы на аналогичные по написанию русские. Обычно решается через меню поиск и замена по всему алфавиту (достаточно муторно). Можно ли это реализовать замену через функцию?
Как быстрое решение:
если перечень позиций известен заранее, то можно просто проверять их на соответствие тем же ВПР, если они не находятся по «эталонному» списку, то проще их заменить вручную на аналогичные позиции, но уже состоящие из кириллицы.

А вообще такие проблемы лучше всего лечить профилактическими беседами с поставщиками, предупреждая подобные случаи.
Зарегистрируйтесь на Хабре , чтобы оставить комментарий

Публикации

Истории