http://myofficelife.liveforums.ru/ My Office Life ru-ru Thu, 29 Aug 2013 15:32:35 +0400 MyBB/mybb.ru http://myofficelife.liveforums.ru/viewtopic.php?pid=5#p5 <p><strong>Как связать несколько таблиц в одной сводной таблице</strong></p> <p><span style="font-style: italic">Описание данной процедуры на примере Excel 2013.</span></p> <p>Файл пример для данного поста скачивайте <a href="http://qps.ru/VoAsh" rel="nofollow" target="_blank">тут</a></p> <p>И так для начала создаём в новой книге несколько таблиц , в которых будут поля по которым их можно будет связать:</p> <p>В нашем примере это таблицы продаж , таблица общих продаж и отдельно таблица продаж портативной техники.<br />Допустим что в сводной таблице нам необходимо связать эти две таблицы.</p> <p><a href="http://uploads.ru/V5bGX.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/V5bGX.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p><a href="http://uploads.ru/2vC67.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/2vC67.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Для того , что бы связать эти две таблицы необходимо создать справочники уникальных значений по тем полям по которым мы будем связывать эти таблицы. На данном примере этими полями будут торговая точка и дата.</p> <p>В Ecxel 2013 создать справочник уникальных значений можно достаточно просто , на новый лист копируем весь список торговых точек с двух таблиц один под другим , далее выделяем весь полученный диапазон и жмём на вкладке ДАННЫЕ кнопку УДАЛИТЬ ДУБЛИКАТЫ. <br />После чего у вас на данном листе останется список уникальных названий торговых точек присутсвующих в двух наших таблицах , теперь из этого списка значений делаем таблицу , которую назовём &quot;Торговые_точки&quot;. Для этого вам необходимо выделить весь справочник перейти на вкладку ВСТАВКА и нажать там ТАБЛИЦА , и переименовать таблицу в &quot;Торговые_точки&quot;.</p> <p><a href="http://uploads.ru/qesn3.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/qesn3.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Далее необходимо создать справочник уникальных дат , делается это ещё проще чем справочник уникальных названий торговых точек, <br />создаём новый лист , назовём его календарь , и делаем там таблицу дат с 01.11.2012 до 30.11.2012 т.к. в наших таблицах из которых мы в итоге будем делать сводную информация о продажах именно за ноябрь 2012 года.<br />Для того что бы сделать справочник дат более функцмональным можем добавить в него помимо даты , так же месяц , номер недели , день недели. Это всё делается несложными формулами и вычисляется от даты к примеру месяц можно вычислить формулой =месяц(A2) если в ячейке A2 стоит какая либо дата , в нашем случае там будет дата 01.11.2012 , номер недели по формуле =НОМНЕДЕЛИ(A2;2) ;2 используется для удобства что бы неделя начиналась с понедельника&#160; ,&#160; день недели =ДЕНЬНЕД(A2;2) ;2 используется так же для удобства что бы 1ым днём недели был понедельник.</p> <p>Далее из этого диапазона дат создаём таблицу , так же как и делали со справочником уникальныз названий торговых точек ( необходимо выделить весь справочник перейти на вкладку ВСТАВКА и нажать там ТАБЛИЦА , и переименовать таблицу в &quot;Календарь&quot;).</p> <p><a href="http://uploads.ru/FAb90.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/FAb90.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>На данном этапе у нас всё готово для создания сводной таблицы на основании двух наших таблиц (таблица общих продаж и отдельно таблица продаж портативной техники). <br />Создаём новый лист , переходим на вкладку ВСТАВКА жмём СВОДНАЯ ТАБЛИЦА. В диалоговом окне &quot;Создание сводной таблицы&quot; ставим переключатель Выберите данные для аналтза на Использовать внешний источник данных.</p> <p><a href="http://uploads.ru/x5KXW.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/x5KXW.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Жмём кнопку выбрать подключение, переходим на вкладку таблицы , выбираем любую из них и жмём ОК.<br />На панели Поля Сводной Таблице переходим на вкладку ВСЕ</p> <p><a href="http://uploads.ru/isov5.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/isov5.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Теперь вы видите список всех созданных ранее нами таблиц, осталось установить связь между ними и работа будет законченна.</p> <p>Для жтого переходим на вкладку АНАЛИЗ жмём кнопку Отношения:</p> <p><a href="http://uploads.ru/O5qPk.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/O5qPk.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>И настраиваем связи наших таблиц данных (таблица общих продаж и отдельно таблица продаж портативной техники) с нашими справочными таблицами (Торговые_точки и Календарь) следующим образом:</p> <p><a href="http://uploads.ru/uaA7J.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/uaA7J.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p><a href="http://uploads.ru/VfezF.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/VfezF.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Так же создаём связи по полю Торговая точка со справочником торговых точек , в итоге получим 4ре связи :<br /><a href="http://uploads.ru/70Fgx.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/70Fgx.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Теперь наша сводная готова можете крутить её в любых разрезах , по дням недели , датам , номерам недели , торговым точкам и т.д. ,<br />справочник торговых точек можно так же было расширить добавив регион к каждой торговой точки , различные группы , выделить обычные магазины и флагманы и т.д.</p> <p>Вот пример готовой сводной таблицы:</p> <p><a href="http://uploads.ru/i9Rv6.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/i9Rv6.jpg" alt="Как связать несколько таблиц в одну свобную таблицу" title="Как связать несколько таблиц в одну свобную таблицу" /></a></p> <p>Примечение:</p> <p>Для построения сводной таблицы в столбцы и строки кидайте поля именно из справочников , т.е. нельзя взять названия торговых точек в строки из таблицы орбщих продаж , только из справочника Торговые_точки, а поля таблиц значений используете только для Значений.</p> mybb@mybb.ru (selesnow) Thu, 29 Aug 2013 15:32:35 +0400 http://myofficelife.liveforums.ru/viewtopic.php?pid=5#p5 http://myofficelife.liveforums.ru/viewtopic.php?pid=4#p4 <p><strong>Создание и использование мультивалютного справочника в Excel</strong></p> <p>Один из примеров практического применения многогранной функции СУММПРОИЗВ</p> <p>Для большей наглядности можете скачать файл который служит примером данного поста&#160; <a href="http://qps.ru/7Xsx2" rel="nofollow" target="_blank">здесь</a></p> <p>Хочу поделиться с Вами одним не сложным приёмом в Excel.</p> <p>Предположим у Вас имеется мультивалютная таблица , в которой необходимо по курсу дня привести сумму к единой валюте , допустим , что у Вас в таблице 4 разные валюты USD , EUR , RUB и привести все продажи необходимо в UAH.</p> <p><a href="http://uploads.ru/bd0Y8.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/bd0Y8.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Вам необходимо заполнить столбец Курс , и исходя из курса на текущий день заполнить столбец Сумма2 суммой продажи в валюте для примера возмём валюту UAH.</p> <p>Для этого необходимо на Лист2 создать справочник курсов валют, следующего вида.</p> <p><a href="http://uploads.ru/LgYRd.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/LgYRd.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Далее всего одной небольшой формулой можно без труда решить данную задачу.</p> <p><a href="http://uploads.ru/GwZer.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/GwZer.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Давайте разберём данную формулу:</p> <p><strong><span style="font-style: italic">=СУММПРОИЗВ((C2&amp;&quot;/&quot;&amp;D2=Лист2!$B$1:$D$1)*(Лист1!A2=Лист2!$A$2:$A$21)*(Лист2!$B$2:$D$21))</span></strong></p> <p>1ая часть формулы <strong><span style="font-style: italic">(C2&amp;&quot;/&quot;&amp;D2=Лист2!$B$1:$D$1)</span></strong> <br />Здесь C2&amp;&quot;/&quot;&amp;D2 является конкатирование пары валют с разделитеме &quot;/&quot; с помощью которого мы разделяем валютные пары в нашем справочнике валют. Результатом этой функции будет на данном примере USD/UAH , это валютная пара которую нам нада найти в справочнике;</p> <p>Далее <strong><span style="font-style: italic"> =Лист2!$B$1:$D$1</span></strong> , указывает на часть справочника с названиями валютных пар в которой нам и необходимо найти нашу валютную пару в данном случае USD/UAH</p> <p><a href="http://uploads.ru/dISkr.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/dISkr.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>2ая часть формулы<strong><span style="font-style: italic"> (Лист1!A2=Лист2!$A$2:$A$21)</span></strong> , Лист1!A2 - указывает на дату на которую нам необходимо из справочника вытянуть курс по нашей валютной паре , в строке 2 валютной парой как я уже писал выше является USD/UAH , датой на которую нам требуется вытянуть курс 01.08.2013.<br />=Лист2!$A$2:$A$2 , указывает на место в нашем справочнике с перечислением дат.</p> <p><a href="http://uploads.ru/OmaVS.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/OmaVS.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>3яя часть формулы <strong><span style="font-style: italic">(Лист2!$B$2:$D$21)</span></strong> указывает на поле значений справочника , т.е. на сами курсы валют . </p> <p><a href="http://uploads.ru/chPvl.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/chPvl.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Таким образом результатом работы данной формулы для ячейки E2 на Лист1 должно быть значение из справочника на пересечении валютной пары USD/UAH и даты 01.08.2013.</p> <p><a href="http://uploads.ru/idHCY.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/idHCY.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Этим значением является 8,1200.</p> <p>Таким образом вы можете создавать валютные справочники с любым колличеством валютных пар, мы можем расширить наш справочник следующим образом.</p> <p><a href="http://uploads.ru/v9oXt.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s5.uploads.ru/t/v9oXt.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Расширим диапазонны в нашей формуле 1 часть формулы теперь будет выглядеть не так<strong><span style="font-style: italic"> (C2&amp;&quot;/&quot;&amp;D2=Лист2!$B$1:$D$1)</span></strong> , а <strong><span style="font-style: italic">(C2&amp;&quot;/&quot;&amp;D2=Лист2!$B$1:$Q$1)</span></strong> в связи с тем что мы добавили больше валютных пар в наш справочник , и он стал шире до столбца Q.</p> <p>Вторая часть формулы <strong><span style="font-style: italic">(Лист1!A2=Лист2!$A$2:$A$21)</span></strong> остаётся неизменно в связи с тем что мы не добавляли дату , и в длинну наш справочник не изменился.</p> <p>3яя часть формулы с областью значений справочника так же расширяется в ширину с <strong><span style="font-style: italic">(Лист2!$B$2:$D$21)</span></strong> до <strong><span style="font-style: italic">(Лист2!$B$2:$Q$21)</span></strong> в связи с тем что с добавлением новых валютных пар расширилась и область значений справочника.</p> <p>Формула в ячейке Е2 на Лист1 теперь будет выглядеть так:</p> <p><strong><span style="font-style: italic">=СУММПРОИЗВ((C2&amp;&quot;/&quot;&amp;D2=Лист2!$B$1:$Q$1)*(Лист1!A2=Лист2!$A$2:$A$21)*(Лист2!$B$2:$Q$21))</span></strong></p> <p>Теперь имея более полный справочник мы можем на Лист1 в столбце D вводить любую из наших валют нашего справочника USD , RUB , EUR , UAH&#160; и смотреть итоговую конвертированную сумму (в стобце F) в любой из этих валют. </p> <p>Я сделаю наш файл немного интерактивным и добавлю возможность изменять валюту конвертации с помощью одной ячейки. Для этого нам необходимо создать справочник возможных валют конвертации , на нашем примере это UAH,USD,EUR,RUB. Делаем список валют на Лист1 в ячейках K2:K5 , в ячейке H2 делаем раскрывающийся список с помощью ДАННЫЕ-ПРОВЕРКА ДАННЫХ-ТИП ДАННХ:СПИСОК-ИСТОЧНИК:=$K$2:$K$5.<br />Далее в столбце D (Валюта конвертации) замени UAH на формулу <strong><span style="font-style: italic">=$H$2</span></strong> , что даст возможность с помощью списка сделанного нами в ячейке H2 выбирать валюту в которой нам необхидимо пересчетать сумму в столбце F (Сумма2)</p> <p>Теперь наша таблица выглядит следующим образом:</p> <p><a href="http://uploads.ru/mn6kr.jpg" rel="nofollow" target="_blank"><img class="postimg" loading="lazy" src="http://s4.uploads.ru/t/mn6kr.jpg" alt="Создание и использование мультивалютного справочника в Excel" title="Создание и использование мультивалютного справочника в Excel" /></a></p> <p>Теперь мы имеем интерактивную таблицу с мультивалютным справочником. Надеюсь Вам понадобится этот приём в практическом применении. </p> <p>Файл можно скачать <a href="http://qps.ru/7Xsx2" rel="nofollow" target="_blank">тут</a> или&#160; <a href="http://qps.ru/JnaiE" rel="nofollow" target="_blank">тут</a><br />29.08.2013</p> mybb@mybb.ru (selesnow) Wed, 28 Aug 2013 18:06:57 +0400 http://myofficelife.liveforums.ru/viewtopic.php?pid=4#p4 http://myofficelife.liveforums.ru/viewtopic.php?pid=3#p3 <p>опа добро пожаловать))))))))))<br />всё обещаю погода будет отличная)</p> mybb@mybb.ru (selesnow) Tue, 09 Aug 2011 15:27:07 +0400 http://myofficelife.liveforums.ru/viewtopic.php?pid=3#p3