Экономим время с Excel

Экономим время с Excel
Категория статьи:
IT, софт

   Часто в работе приходиться сталкиваться с тем, что нужно собрать данные из двух разных таблиц и сгруппировать их по какому-либо признаку. Хорошо, если в них от силы 5-10 позиций, а если тысячи? Это же работы не на один день! Лично я уважаю свое рабочее время и вообще ленивый человек. А лень, на мой взгляд, это двигатель прогресса. Поэтому я стараюсь максимально использовать в работе все возможности Excel. В частности, меня очень выручают две функции ИНДЕКС () и ПОИСКПОЗ (). Поделюсь с вами своим опытом.

    Например, существует некий холдинг, в который входит несколько компаний. Все они находятся в одном здании. И есть компания, которая сдает этому Холдингу помещения в аренду. Сотрудников много, все сидят в разных кабинетах и занимают при этом разную площадь:

Исходная таблица 1

    И есть на другом листе список сотрудников с указанием места их работы (контрагента):

Исходная таблица 2

   Требуется посчитать стоимость аренды помещений для каждого контрагента.

   Для начала рассчитаем стоимость аренды по каждому сотруднику (стоимость 1 м2 указана). Думаю, не имеет смысла рассказывать, как это сделать. Умножать в Excel умеют все. В итоге получится вот такая таблица:

Таблица2 с дополнением

   Теперь в эту же таблицу справа добавим столбец и назовем его Контрагент. Сюда должно попасть место работы каждого сотрудника из второй таблицы. Сначала найдем, в на какой позиции во второй таблице находится ФИО сотрудника. Для этого в Excel существует функция ПОИСКПОЗ() со следующими аргументами:

    1.Искомое значение. В нашем случае это ФИО сотрудника из первой таблицы - Лист1!$B2.

    2.Просматриваемый массив. Это тот диапазон данных, в котором Excel будет искать наше искомое значение - Лист2!$B$2:$B$13.

    3.Тип сопоставления. Имеет 3 значения: 0 – Точное совпадение, 1 – Меньше искомого значения, -1 – Больше искомого значения. Ставим 0.

    У нас должна получиться вот такая формула:

=ПОИСКПОЗ(Лист1!$B2;Лист2!$B$2:$B$13;0)

    Протягиваем формулу и получаем вот такой результат:

Таблица 2 с новыми данными

    В столбце Контрагент появились цифры. Это номер позиции сотрудников во второй таблице. Иными словами, Филимонов А. А. в списке находится под номером 12, Гаврилова С. М. – под номером 2 и так далее.

    Теперь сопоставим номер позиции сотрудника с контрагентом, указанным напротив него.

    В предыдущую формулу в начало после знака «=» добавим еще одну функцию - ИНДЕКС (). Она возвращает значение на пересечении указанных строки и столбца и содержит следующие аргументы:

    1.Массив – это тот диапазон, из которого нам нужно получить конечный результат, то есть наименование контрагента, соответствующее конкретному сотруднику: Лист2!$C$2:$C$13.

    2.Номер строки, в которой Excel будет искать нужное нам значение. Вот здесь нам и пригождается предыдущая формула. Она и будет искать номер строки.

   Закрываем все необходимые скобки и получаем формулу:

 =ИНДЕКС(Лист2!$C$2:$C$13;ПОИСКПОЗ(Лист1!$B2;Лист2!$B$2:$B$13;0))

    Протягиваем ее вниз до конца нашей первой таблицы:

Таблица 1 конечная

    Теперь напротив ФИО каждого сотрудника указано место его работы. И можно рассчитать стоимость аренды помещения по каждому контрагенту. Делаем сводную таблицу и, используя функцию СУММЕСЛИ () собираем данные. В результате у нас получится вот что:

Таблица 3

    Можно выставлять счет-фактуры и собирать выручку.

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

ОЦЕНИТЕ полезность статьи:
100/100
Нет комментариев. Ваш будет первым!
Загрузка...
Использование материалов данного сайта допускается только с указанием активной гиперссылки на источник с данного сайта.