Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

Must-know (должен знать каждый!) Пожалуй порядка 70% вопросов, ежедневно задаваемых посетителями форума "Профессиональные приемы работы в Microsoft Excel", решается с помощью функции ВПР(). Функция ВПР() позволяет извлекать данные из таблицы-справочника по коду записи. Например, зная артикул товара, можно без ручного поиска или макросов извлечь из прайс-листа его цену или другую характеристику.

Здравствуйте, уважаемые читатели.

Сегодня разберем задачу, предложенную нашим читателем. Вот его письмо:

Здравствуйте!

С недавних пор стал подписчиком Вашей рассылки и дискуссионного листа. Рад,
что существует такая возможность лучше
исследовать возможности Excel, научиться новому, решить накопившиеся
проблемы.

У меня есть вопрос - проблема, которую я не знаю, как решить. Суть в
следующем:

а) есть некая таблица - рейтинг банков по определенному показателю - на
первом месте - лучший результат, ниже -
следующий, и т.д. Таблица составляется состоянием на некое число -
например, первое число каждого месяца. В таблице -
около 150 строк с информацией.
б) необходимо отследить изменение показателя для каждого конкретного банка
и выразить его в абсолютном и относительном
виде.

В результате имеем следующее:
например
состоянием на 1.01.2006 г. - лист "01.01.2006"

Банк А 500
Банк Б 490
Банк В 480
Банк Г 450
Банк Д 420

состоянием на 1.02.2006 г. - лист "01.02.2006"

Банк Б 510
Банк А 480
Банк В 470
Банк Д 450
Банк Г 430
(т.е. расположение банков в следующей таблице - хаотичное).

Если бы расположение банков в обоих листах было в одинаковом порядке -
никаких проблем! Пишем одну формулу , копируем ее
вниз - и все. А так - приходится (мне) каждый раз после записи формулы на
новом листе и копирования ее вниз
корректировать для каждого банка значение ячейки из предыдущего листа
(надеюсь, я понятно объясняю :-)) А строк, как я
уже говорил, - около 150, причем из кол-тво - непостоянно, т.к. в очередной
таблице может не быть значения по тому или
иному банку (или может не быть значения по банку в предыдущей таблице) ,а
таблиц таких - 6. В результате - несколько
часов тупой работы, которую , я уверен, можно было бы упростить, если бы я
знал что-то, чего я не знаю об Excele.

Возможно ли в формулу типа (А - В)/В *100% , где А - значение показателя
для банка А из листа, например, "01.02.2006", а
Б - значение того же показателя для того же банка А из предыдущего листа,
например, "01.01.2006" как-то преобразовать,
чтобы формула учитывала,что из предыдущего листа надо выбирать именно
значение, соответствующее нужному банку?

Заранее премного благодарен.

С уважением,
Василий Х.

Уважаемый Василий!

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

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

В любую расчетную ячейку, введем формулу вида:

=ВПР(Искомоезначение;Таблицаисходныхданных;Номерстолбца;0)

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

* В тексте встречаются выражения "функция возвращает значение", "Функция вернет ошибку" и т.д. Это означает, что результатом функции будет указанное значение. Мы как бы запрашиваем у компьютера с помощью функции ответ на какой-либо вопрос. Компьютер через функцию возвращает нам ответ.
* Чтобы компьютер знал условия задачи, мы сообщаем функции АРГУМЕНТЫ. В нашем примере функция =ВПР() имеет агрументы Искомоезначение, Таблицаисходныхданных, Номерстолбца и 0. Аргументы функций Excel могут ЗАДАВАТЬСЯ числами, текстом (заключенным в кавычки), ссылками на ячейки или диапазоны ячеек либо другими ВЛОЖЕННЫМИ функциями. Функция может и не иметь аргументов. Такова, например, функция =Сегодня(), ВОЗВРАЩАЮЩАЯ текущую дату.


Формула для третьей строки может иметь следующий вид:

=ВПР(A3;'01.02.2006'!$A$3:$G$153;4;0)

Функция будет искать в первом столбце диапазона $A$3:$G$153 на листе '01.02.2006' (в исходной таблице) банк, название которого хранится в ячейке A3 итогового листа.
Если банк будет найден, ячейка с формулой примет значение из 4-го столбца исходной таблицы.

Обратите внимание на знаки доллара в обозначении исходного диапазона.
Это признак абсолютной ссылки. При копировании нашей формулы в 4-ю ячейку для первого аргумента произойдет смещение - вместо A3 мы получим A4.
А вот второй аргумент, заданный АБСОЛЮТНОЙ (зафиксированной) ссылкой, останется в неизменном виде. Чтобы ввести абсолютную ссылку, вовсе не нужно руками проставлять значки $ (хотя, если очень хочется...). Как обычно введите диапазон в формулу с помощью мыши и нажмите клавишу [F4]. Все! Ссылка преобразована в абсолютную.
Подробнее о типах ссылок - в одном из следующих выпусков.

0 в воследнем аргументе - запрет неточного поиска. Если в нашем примере в исходной таблице не будет данных по банку, название которого находится в ячейке A3, то функция вернет ошибку. Если бы аргумент был равен единице, функция нашла бы банк, название которого максимально близко похоже на искомое название.

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

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

Если формулы введены правильно, везде, где нужно, настроены абсолютные ссылки, в каждой строке будут рассчитаны правильные результаты.

Еще одна тонкость. Если нужного банка не окажется в исходной таблице, формула примет ошибочное значение #Н/Д. Можно обойти эту ошибку, использовав функции =ЕОШИБКА() и =ЕСЛИ().

Получится громоздкая, но работающая конструкция типа

=(ЕСЛИ(ЕОШИБКА(ВПР(A3;'01.02.2006'!$A$3:$G$153;4;0));0;A3;ВПР('01.02.2006'!$A$3:$G$153;4;0))

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

Пример использования функции =ВПР() в формате rar/xls (4,46 Кб) Вы найдете по адресу http://studenttools.narod.ru/excel/vpr.rar.