7-го мая я получил любопытное письмо от одной из читательниц нашего блога – Надежды Громовой. Вот что она пишет:

Здравствуйте, Илья! Спасибо большое за Ваши заметки. Всегда с удовольствием их читаю. Очень познавательно, конкретно и практично!
Вы несколько раз рассказывали про эксель и я подумала, что, может быть, Вы сможете мне помочь? Наша компания занимается торговлей. Поставщики еженедельно присылают нам свои прайсы и нам хотелось бы отслеживать изменения в них: на какие товары цены выросли, на какие снизились. Для нас это очень важно.
На первый взгляд проблема кажется пустяковой и должна решаться одной формулой. Но бывает так, что сегодня в прайсе у поставщика 400 позиций, а завтра – 600, сегодня одни наименования, а завтра – другие. В итоге приходится сравнивать прайсы практически вручную, а это часов 5-6 каждую неделю. Обидно так бездарно тратить своё время…
Я уже не говорю про то, чтобы «мониторить» прайсы конкурентов – это для нас пока совсем несбыточная мечта.
Я подозреваю, что эксель может сделать сравнение прайсов автоматически, но я не знаю как. Мой программист говорит, что надо писать макрос, но он сам с макросами никогда не работал.
Я нисколько не сомневаюсь в квалификации нашего программиста, и все же, решила обратиться к Вам за советом: нужно ли мне заказывать написание макроса или есть какие-либо стандартные решения в экселе или других программах?
Заранее благодарю за любую помощь!
С уважением.
Громова Надежда Александровна

Здравствуйте, Надежда!

Эта проблема решается абсолютно стандартно и никаких макросов писать не надо. При грамотном подходе сравнение двух прайс-листов (пускай даже там по 10 000 позиций) должно занимать у Вас не более 10 минут.


Итак – решение Вашей проблемы

По факту для решения проблемы надо разобраться с двумя задачами:

• Номенклатура в прайсах не всегда совпадает.
• Из-за того, что номенклатура не совпадает – сравнивать цены можно только вручную.

Жаль конечно, что Вы не прислали прайсы Ваших поставщиков. Но это не беда. Для демонстрации мы позаимствовали кусок прайса на сайте компании Трилайн и немного переделали его (я надеюсь, что они на нас за это не обидятся).

Предположим, что у нас есть два прайса – от 4 и от 11 мая 2010 года. Часть товарных позиций в прайсах совпадает, а часть – нет. Пускай это выглядит так:


Прайс от 4 мая 2010 года

Первое действие, которое нам понадобится выполнить для сравнения прайсов – создать список, в котором будут присутствовать все товарные позиции, но не будет дублей. Для этого надо создать в рабочей книге Excel ещё один лист, после чего скопировать на него списки товаров и из первого и из второго прайса. Пускай это будет выглядеть так:

Так как данные скопированы из двух прайсов, в которых есть одинаковые позиции – в списке есть дубли. Например модели под номерами 1 и 18, 7 и 23 или 15 и 30 (но это далеко не полный список дублей).

Понятно, что дубли можно выбрать вручную, используя автофильтр. Но это возможно в прайсе из 30 позиций, а если их 300!? Теперь мне становится понятно, почему у Надежды уходит на это по 5-6 часов на анализ прайсов.

Чтобы сэкономить время - от дублей мы будем избавляться автоматически. Для этого надо проделать следующие манипуляции:

Выделите весь список товаров, включая название диапазона (в данном случае – это слово «Модели»). После этого перейдите в меню Данные – Фильтр – Расширенный фильтр

На экране появится диалоговое окно, в котором надо отметить три вещи:

1. Скопировать результат в другое место. Если этого не сделать Excel отфильтрует список на месте и не исключит дубли, а просто скроет их.

2. Поместить результат в диапазон. Выберите место, куда хотите записать результат. Я указал ячейку D4. Когда Excel отфильтрует список – он запишет в эту ячейку название диапазона (в данном случае – это слово «Модели»), а сам список моделей «развернёт» вниз.

3. Но самое главное в этом окне – поставить галочку «Только уникальные записи». Именно она отвечает за то, чтобы автоматически исключить дубли.

Нажимаем кнопку «ОК» и начиная с ячейки D4 получаем список без дублей. Выглядит это примерно так:

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

Последнее, что нам осталось – это автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого можно было бы воспользоваться функцией СуммЕсли, но мы её уже разбирали, а писать два раза про одно и то же – неинтересно.

Поэтому мы с Вами разберём формулу вопроса. Она отличается от функции СуммЕсли тем, что работает не только с цифровыми, но и с любыми другими значениями (текст, дата и т.д.).

Записывается функция следующим образом:

=ВПР( искомое_значение; таблица; номер_столбца; интервальный _просмотр)

С помощью этой функции нам надо автоматически вытащить из таблицы прайса в таблицу сравнения цену определённого товара. Давайте разберём, как работает эта функция и запишем её в нашу таблицу.

Первый параметр (искомое_значение) – это строчка, которую мы будем искать в таблице прайса. Очевидно, что легче всего искать товары по их наименованию.

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

Третий аргумент (номер_столбца) – это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы с Вами определили таблицу из двух столбцов. Цена содержится во втором из них. И неважно, что на листе – это столбец номер три, в выделенном диапазоне этот столбец – второй!

И наконец – последний аргумент (интервальный_просмотр). Здесь всё просто. Если таблица, в которой Вы ищете значение отсортирована (по возрастанию или по убыванию) – надо ставить значение ИСТИНА, если не отсортирована – пишите ЛОЖЬ, иначе функция будет работать с ошибками.

Всё. Формула готова. Теперь осталось только «протянуть» её вниз. Перед тем, как «протягивать» формулу – не забудьте закрепить диапазоны! Дело в том, что при протягивании формулы вниз будет смещаться вниз и таблица. Чтобы этого не произошло – поставьте перед буквой столбца и перед номером строки значок доллара (это можно сделать, выделив нужный диапазон и нажав клавишу F4).

Теперь у нас всё готово. Вот что получилось в итоге:

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

Файл с этим примером Вы можете скачать здесь

И напоследок.

В своих статьях я показываю лишь мизерную часть возможностей Excel. Однако, применив только этот инструмент в своей работе, вы уже сэкономите достаточно времени и сил, чтобы увидеть результат. А если вы хотите сделать большой рывок и вывести свою работу на новый, осознанный уровень, хотите научится собирать любые управленческие отчёты любой сложности буквально в 2 клика, то предлагаю вашему вниманию мой флагманский видеокурс "Бюджетирование и управленческий учёт в Excel".

Посмотреть подробное описание тренинга можно на сайте

Задать интересующие вопросы можно по телефону 8 800 201-49-35.