Волшебная формула⁠⁠

Всем привет. Продолжаем постигать Excel. Если Вы не знакомы с моими предыдущими уроками, то советую ознакомиться. Сегодня я хочу продолжить тему, поднятую в предыдущем посте Как я делаю шаблоны о составлении шаблонных форм. Однажды мне потребовалось сделать такой лист, содержимое которого зависит от названия листа. Я нашел одну чудесную формулу и хочу ею с Вами поделиться. Также дополнительно я хочу показать, как в простом листе запутать формулы так, чтобы потом в них было практически невозможно разобраться. Спросите, зачем это нужно? Иногда бывает полезно – скидываешь человеку таблицу и если в ней нужно что-то переделать, то он опять обращается к Вам, только не забудьте сохранить у себя исходник. Но обо всем по порядку.

Сначала немного теории.
Рассмотрим работу функции ЯЧЕЙКА (тип_сведений;[ссылка]), она имеет 2 аргумента: тип сведений и ссылку на ячейку соответственно. Сейчас нас интересует тип «имяфайла», выбираем его и ссылаемся на саму ячейку, где мы это пишем (остальные типы вам на самостоятельное изучение). Для корректной работы этой функции необходимо чтобы файл был сохранен где-нибудь. Итак, в имени файла мы видим непосредственно имя файла и после него имя листа, на котором прописываем эту функцию (точнее на лист, куда ссылаемся ссылкой). Давайте попробуем вырезать то, что нам нужно, а именно имя листа. Для этого нам нужно знать длину текста в ячейке с именем файла, получаем ее с помощью функции ДЛСТР(),затем нам нужно найти позицию закрывающейся квадратной скобки, которая ограничивает имя файла при помощи функции ПОИСК, разница этих чисел и будет длиной имени листа. Отрезаем справа от первоначальной ячейки эту длину и получаем ячейку с именем файла. В результате у нас должно получиться нечто подобное.

Формулы при этом выглядят так:

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

назавем этот лист  «База».
Здесь рассмотрим еще одну полезную функцию Excel, которая называется именованные области (или как-то так). Выделяем область к которой нам нужно будет часто обращаться, в нашей таблице это столбцы от А до G на листе «База», и жмакаем на строку которая расположена слева от значка формулы

теперь пишем там название нашей области «база».
Все, теперь в формулах можно вместо ссылки на область эту область писать просто база! Пишем в лист с волшебной формулой следующие формулы:

помним что в ячейке D5 у нас название листа, которое будет искаться в первом столбце базы, то есть для создания карточки сотрудника нам нужно будет назвать лист как фамилию нужного нам сотрудника. Я решил не просто тупо подставлять значения из таблицы (это же скучно), а склеить ФИО, вместо дня рождения выводить сколько полных лет, а вместо даты приема на работу стаж в годах. Но это просто в образовательных целях. Можно и эти данные в формуле прописать в базе.
Теперь проверим, назовём лист «Иванов»:

Во как!
на основании такого листочка и нехитрого макроса я на пример делал много отчетов с разбивкой по пятидневкам или по дням из месячной базы. Но создание макросов я намеренно не рассматриваю в своих статьях (сам только учусь их писать)
Теперь перейдем ко второй части нашего балета. То есть запутаем эту формулу так, чтобы сами не могли ничего разобрать. Смотрите, у нас в ячейке D1  прописана формула, ссылающаяся в принципе на любую ячейку этого листа, для удобства поменяем чтобы она ссылалась на ячейку A1. Теперь посмотрим, кто как у нас на этом листе ссылается: ставим курсор на A1, идем в пункт формулы, нажимаем «зависимые ячейки» несколько раз, получаем:

видим что все формулы зависят от А1. Теперь идем в D5, копируем все что там после знака «=», нажимаем поиск с заменой (Ctrl+H), пишем Найти D5, заменить на то что скопировали ПРАВСИМВ(D1;D4). Заменяем везде, затем идем в D4, копируем все что после знака «=»…. и так повторяем пока не придем в ячейку А1. После этого ячейки D1-D5 можно удалить, они у нас были как бы промежуточные. В итоге лист у нас работает также как и раньше, а что же в формулах? Заглянем в B4: «@=ВПР(ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-ПОИСК("]";ЯЧЕЙКА("имяфайла";A1);1));база;1;0)&" "&(ВПР(ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-ПОИСК("]";ЯЧЕЙКА("имяфайла";A1);1));база;2;0)&" "&ВПР(ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-ПОИСК("]";ЯЧЕЙКА("имяфайла";A1);1));база;3;0))»
жуть какая! ничего не разберешь. но мы то с вами знаем что формула ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-ПОИСК("]";ЯЧЕЙКА("имяфайла";A1);1)) просто показывает нам имя листа. И это у нас относительно простая зависимость была изначально. Посредством таких вставок с заменой мы убираем промежуточные вычисления, что очень сильно ухудшает читаемость формулы.
вот пример на гугл. докс., но только он не работает потому что файл не сохранен на диск, сохраните и ковыряйте
https://drive.google.com/file/d/0B8QwhfN2DgusTzIxQ1ZHTlZtclc...
На этом пока все, в следующий раз наверное все таки будут сводные таблицы (по многочисленным просьбам)