И так для начала создаём в новой книге несколько таблиц , в которых будут поля по которым их можно будет связать:
В нашем примере это таблицы продаж , таблица общих продаж и отдельно таблица продаж портативной техники. Допустим что в сводной таблице нам необходимо связать эти две таблицы.
Для того , что бы связать эти две таблицы необходимо создать справочники уникальных значений по тем полям по которым мы будем связывать эти таблицы. На данном примере этими полями будут торговая точка и дата.
В Ecxel 2013 создать справочник уникальных значений можно достаточно просто , на новый лист копируем весь список торговых точек с двух таблиц один под другим , далее выделяем весь полученный диапазон и жмём на вкладке ДАННЫЕ кнопку УДАЛИТЬ ДУБЛИКАТЫ. После чего у вас на данном листе останется список уникальных названий торговых точек присутсвующих в двух наших таблицах , теперь из этого списка значений делаем таблицу , которую назовём "Торговые_точки". Для этого вам необходимо выделить весь справочник перейти на вкладку ВСТАВКА и нажать там ТАБЛИЦА , и переименовать таблицу в "Торговые_точки".
Далее необходимо создать справочник уникальных дат , делается это ещё проще чем справочник уникальных названий торговых точек, создаём новый лист , назовём его календарь , и делаем там таблицу дат с 01.11.2012 до 30.11.2012 т.к. в наших таблицах из которых мы в итоге будем делать сводную информация о продажах именно за ноябрь 2012 года. Для того что бы сделать справочник дат более функцмональным можем добавить в него помимо даты , так же месяц , номер недели , день недели. Это всё делается несложными формулами и вычисляется от даты к примеру месяц можно вычислить формулой =месяц(A2) если в ячейке A2 стоит какая либо дата , в нашем случае там будет дата 01.11.2012 , номер недели по формуле =НОМНЕДЕЛИ(A2;2) ;2 используется для удобства что бы неделя начиналась с понедельника , день недели =ДЕНЬНЕД(A2;2) ;2 используется так же для удобства что бы 1ым днём недели был понедельник.
Далее из этого диапазона дат создаём таблицу , так же как и делали со справочником уникальныз названий торговых точек ( необходимо выделить весь справочник перейти на вкладку ВСТАВКА и нажать там ТАБЛИЦА , и переименовать таблицу в "Календарь").
На данном этапе у нас всё готово для создания сводной таблицы на основании двух наших таблиц (таблица общих продаж и отдельно таблица продаж портативной техники). Создаём новый лист , переходим на вкладку ВСТАВКА жмём СВОДНАЯ ТАБЛИЦА. В диалоговом окне "Создание сводной таблицы" ставим переключатель Выберите данные для аналтза на Использовать внешний источник данных.
Жмём кнопку выбрать подключение, переходим на вкладку таблицы , выбираем любую из них и жмём ОК. На панели Поля Сводной Таблице переходим на вкладку ВСЕ
Теперь вы видите список всех созданных ранее нами таблиц, осталось установить связь между ними и работа будет законченна.
Для жтого переходим на вкладку АНАЛИЗ жмём кнопку Отношения:
И настраиваем связи наших таблиц данных (таблица общих продаж и отдельно таблица продаж портативной техники) с нашими справочными таблицами (Торговые_точки и Календарь) следующим образом:
Так же создаём связи по полю Торговая точка со справочником торговых точек , в итоге получим 4ре связи :
Теперь наша сводная готова можете крутить её в любых разрезах , по дням недели , датам , номерам недели , торговым точкам и т.д. , справочник торговых точек можно так же было расширить добавив регион к каждой торговой точки , различные группы , выделить обычные магазины и флагманы и т.д.
Вот пример готовой сводной таблицы:
Примечение:
Для построения сводной таблицы в столбцы и строки кидайте поля именно из справочников , т.е. нельзя взять названия торговых точек в строки из таблицы орбщих продаж , только из справочника Торговые_точки, а поля таблиц значений используете только для Значений.
Создание и использование мультивалютного справочника в Excel
Один из примеров практического применения многогранной функции СУММПРОИЗВ
Для большей наглядности можете скачать файл который служит примером данного поста здесь
Хочу поделиться с Вами одним не сложным приёмом в Excel.
Предположим у Вас имеется мультивалютная таблица , в которой необходимо по курсу дня привести сумму к единой валюте , допустим , что у Вас в таблице 4 разные валюты USD , EUR , RUB и привести все продажи необходимо в UAH.
Вам необходимо заполнить столбец Курс , и исходя из курса на текущий день заполнить столбец Сумма2 суммой продажи в валюте для примера возмём валюту UAH.
Для этого необходимо на Лист2 создать справочник курсов валют, следующего вида.
Далее всего одной небольшой формулой можно без труда решить данную задачу.
1ая часть формулы (C2&"/"&D2=Лист2!$B$1:$D$1) Здесь C2&"/"&D2 является конкатирование пары валют с разделитеме "/" с помощью которого мы разделяем валютные пары в нашем справочнике валют. Результатом этой функции будет на данном примере USD/UAH , это валютная пара которую нам нада найти в справочнике;
Далее =Лист2!$B$1:$D$1 , указывает на часть справочника с названиями валютных пар в которой нам и необходимо найти нашу валютную пару в данном случае USD/UAH
2ая часть формулы (Лист1!A2=Лист2!$A$2:$A$21) , Лист1!A2 - указывает на дату на которую нам необходимо из справочника вытянуть курс по нашей валютной паре , в строке 2 валютной парой как я уже писал выше является USD/UAH , датой на которую нам требуется вытянуть курс 01.08.2013. =Лист2!$A$2:$A$2 , указывает на место в нашем справочнике с перечислением дат.
3яя часть формулы (Лист2!$B$2:$D$21) указывает на поле значений справочника , т.е. на сами курсы валют .
Таким образом результатом работы данной формулы для ячейки E2 на Лист1 должно быть значение из справочника на пересечении валютной пары USD/UAH и даты 01.08.2013.
Этим значением является 8,1200.
Таким образом вы можете создавать валютные справочники с любым колличеством валютных пар, мы можем расширить наш справочник следующим образом.
Расширим диапазонны в нашей формуле 1 часть формулы теперь будет выглядеть не так (C2&"/"&D2=Лист2!$B$1:$D$1) , а (C2&"/"&D2=Лист2!$B$1:$Q$1) в связи с тем что мы добавили больше валютных пар в наш справочник , и он стал шире до столбца Q.
Вторая часть формулы (Лист1!A2=Лист2!$A$2:$A$21) остаётся неизменно в связи с тем что мы не добавляли дату , и в длинну наш справочник не изменился.
3яя часть формулы с областью значений справочника так же расширяется в ширину с (Лист2!$B$2:$D$21) до (Лист2!$B$2:$Q$21) в связи с тем что с добавлением новых валютных пар расширилась и область значений справочника.
Формула в ячейке Е2 на Лист1 теперь будет выглядеть так:
Теперь имея более полный справочник мы можем на Лист1 в столбце D вводить любую из наших валют нашего справочника USD , RUB , EUR , UAH и смотреть итоговую конвертированную сумму (в стобце F) в любой из этих валют.
Я сделаю наш файл немного интерактивным и добавлю возможность изменять валюту конвертации с помощью одной ячейки. Для этого нам необходимо создать справочник возможных валют конвертации , на нашем примере это UAH,USD,EUR,RUB. Делаем список валют на Лист1 в ячейках K2:K5 , в ячейке H2 делаем раскрывающийся список с помощью ДАННЫЕ-ПРОВЕРКА ДАННЫХ-ТИП ДАННХ:СПИСОК-ИСТОЧНИК:=$K$2:$K$5. Далее в столбце D (Валюта конвертации) замени UAH на формулу =$H$2 , что даст возможность с помощью списка сделанного нами в ячейке H2 выбирать валюту в которой нам необхидимо пересчетать сумму в столбце F (Сумма2)
Теперь наша таблица выглядит следующим образом:
Теперь мы имеем интерактивную таблицу с мультивалютным справочником. Надеюсь Вам понадобится этот приём в практическом применении.
