Сводная таблица по нескольким диапазонам данных

3 способа сделать сводную в Excel по нескольким таблицам исходных данных. Скачать пример www.planetaexcel.ru/technique...
Заходите в гости ↓↓↓
Twitter / planeta_excel Facebook / planetaexcel Instagram / planetaexcel Мои книги planetaexcel.ru/books/ Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/

[@26Tudika]

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

[@user-iv6ji7fk7y]

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

[@user-wl8cw4fm8z]

Николай, здравствуйте. Большой респект вам за видео, кратко, понятно, доступно, просто супер.

[@gelenlove]

Николай, спасибо! Всё так понятно и позитивно!!!

[@planetaexcel]

Оптимизм - наш долг! :)

[@user-wc8xl6tf5d]

Доброго времени. Огромное желание освоить таблицы уже долго сидело в голове, благодаря вам оно начало воплощаться, все доступно и понятно рассказано, Благодарю 👍

[@user-wh3on6px2o]

Спасибо большое! Шикарный материал!

[@user-si9id4yn4m]

Спасибо за это видео. Очень помогло. Не мог файл сделать из-за слишком большого количества строк, а ролик с power query прям спас ситуацию ))

[@stz4448]

Николай спасибо большое! Благодарю

[@biweb13]

Николай! Спасибо за Способ 1 - Power Query, все остальные на мой взгляд нормально устарели после появления Power Query, но знать что раньше эту задачу решали способами 2 или 3 полезно... это история развития Excel...

[@alisakireulishvili2381]

Спасибо Николай! Вы гений!

[@user-tk6of3cv4z]

Супер способ! у меня были листы по 900к строк из который надо было собрать один сводник, по первому способу прекрасно получилось за 10 минут!! спасибо огромное!!!!!!!!!!!!!!!!!!!!!!!! а то страшно подумать, что за садо-мазо пришлось бы делать без этого

[@Gen_Win_]

В третьем варианте, удобство в том, что если щелкнуть на пересечении итогов, то таблица разложиться в плоскую и одномерную (UNPIVOT). Как раз искал где этот мастер, а он вот он где. Как говорил один клиент - спасибо что Вы есть! :)

[@yurd563]

Большое спасибо за видеоурок!

[@user-kz2ns5tr6d]

Очень помогли, благодарна от всей души.

[@user-cd6qt6bl7t]

Класс! Будем пробовать)

[@Albertus99]

Большое спасибо Николай

[@user-ml6rm8ve2t]

Спасибо! Это то, что нужно!)

[@user-hk4sk2ti5c]

У автора очень приятный слог

[@CaHeKRU]

Все по полочка, спасибо!

[@MrFima54374]

Спасибо большущее за Вашу работу Николай. Нужна подробная инструкция по созданию стилей сводной таблицы.

[@cyklonification]

Like! Спасибо за ролик!

[@PP-dh5uq]

Большое спасибо! все получилось. Но изначально нужно скачать надстройку для excel

[@user-vy9ni4tv8m]

Спасибо за видео!!!!!

[@user-es2eo9zf6f]

Николай спасибо

[@seila1777]

Эх, как мне нужно такое качественное видео по МАС. Где найти? Смотрю эти видео (все!)) из-за качества. Получаю удовольствие)

[@planetaexcel]

По Excel для Mac или просто по работе на Mac?

[@seila1777]

Nikolay Pavlov Хотя бы по таблицам Numbers. Я бы рада была. Некоторых опций сильно не хватает и я не могу поверить что это и не заложено. Например, шикарная фишка экселя по ссылкам на другие ячейки и другие листы отсутствует, насколько я понимаю на МАСе. Как жить?))))

[@planetaexcel]

Numbers - это прошлый век. Поставьте себе хотя бы Microsoft Office for Mac 2016 - и будет вам счастье :)

[@seila1777]

Nikolay Pavlov :(

[@Period79]

@@planetaexcel по мне так прошлый век Ексель. Numbers в плане визуализации, положит Ексель на лопатки. Я свои техзадания и расчёты выполняем в нём, хотя начинал в Екселе, который знаю очень хорошо. Десяток таблиц на одном листе, в перемешку с графиками, схемами, зарисовками, делают мой продукт очень дорогим. Смотря какие задачи решаем

[@roymanzed2969]

Привет спасибо большое

[@SergeyMazurkin]

прикольно. спасибо.

[@trustme9727]

Огонь!

[@user-oz1ku3in9o]

Классно!

[@narimantagiev]

хорошие уроки

[@tulaganovmirsardor]

Николай, добрый день и спасибо за данный урок! Не могли бы вы подсказать мне аналогичные KZbin проекты, в которых обучают приёмам работы с Word, PowerPoint и другим ПО MS Office?

[@user-sj8gh2cs8i]

Спасибо за пособие, помогло, я применил источник данных из онлайн google таблиц, вроде разобрался! У гугла тоже есть сводная, но локализация на англ. и функционал не понятен.

[@vadimkokuev8510]

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

[@Youriy2010]

Не можно, а нужно.

[@Albertus99]

Спасибо

[@sevakworld]

Здравствуйте. Интересно, можно ли в PQ (или Excel) собрать несколько таблиц в одну, и чтобы собранная таблица была обратно совместима с раздельными. Скажем изменил данные в собранной таблице и данные появились в раздельной таблице (конечно речь идёт только о редактировании данных, а не добавлении новых).

[@user-im2dr9jb1c]

Николай, спасибо за ваши супер полезные видео! Помогают экономить массу времени. Подскажите, пожалуйста, как сделать сводную из двух совершенно разных листов, при том что шапки, данные и размеры разные. Есть только общий параметр - товар?

[@planetaexcel]

Можно загрузить обе таблицы в Модель данных (Power Pivot), настроить связи и построить потом обычную сводную, как будто это одна таблица - см. www.planetaexcel.ru/techniques/8/1233/

[@user-qc8ye6cj8l]

Здравствуйте! Подскажите пожалуйста как воспользоваться формулами которые Excel предлагает списком ниже строки формул?

[@litwinczuk_jd]

1:56 Power Query 9:48 макрос с SQL командой UNION 12:46

[@user-hs1of2zb1k]

спасибо

[@user-sq4iz6qj3w]

Есть ли у вас видео об обединении однотипних файлов, в каждом из которых находятся разные (не однотипные) вкладки. Возможно ли обединить все в один файл но з разбивкой по вкладкам?

[@alekseyirish]

Николай, приветствую! Создал подключение по первому варианту: сделал книгу в которой в двух вкладках (11 и 12 декабря) разместил все данные. Способ сработал. Добавил третью вкладку 10 декабря с данными и обновил подключение. Как итог: в книге со сводной таблицей появилось подключение вкладки 10 декабря, но без данных. Что пошло не так?

[@ludmilamoiseeva3598]

Отлично, других комментариев нет.

[@alexandermihailov9290]

Здравствуйте! Подскажите, можно ли связать в одну сводную таблицу столбцы с разных листов или разных именованных диапазонов? На первом листе столбец дата, на втором продажи, а в третьем город и всё это соединить в одну сводную таблицу.

[@Youriy2010]

А в чем проблема объединить эти столбцы на новом листе в новую таблицу и по ней уже построить сводную? Или сделать это в редакторе Power Query?

[@user-kj2tb8bp7j]

Добрый день, Николай! Спасибо за материал. На 4:00 вы отключаете навигацию, но у меня не получается повторить это действие за вами. еще раз спасибо за обучение и, пожалуйста, подскажите с чем это может быть связано.

[@avm_zp]

Аналогично. Никак не могу удалить пункт "Навигация". В итоге дальше выдает ошибку.

[@user-pg6nz6kw6t]

я также не могу отключить кнопку навигация (((

[@user-dd1zu1tk8h]

Здравствуйте, подскажите пожалуйста, а как в облаке в екселе сделать отдельную вкладку которая будет собирать автоматически информацию по предыдцщим вкладкам( наименование товара , остаток товара, цена)

[@user-wo5bx6bt7u]

Добрый вечер) Спасибо Николай за урок Сводная таблица из несколько диапазонов данных)Хотела уточнить если не удаляется шаг навигация(нету даже хрестика),как быть? Как его можно удалить? Спасибо)

[@mushomkrtchyan527]

Извените как Вы решили проблему с навигацией?

[@topmazila8766]

а возможно создать таблицу, для получения суммы прибыли за месяц по разным предприятиям, с учётом выполнения работы в определённые дни недели, и с учётом разных объёмов работ? Пример: по понедельникам и средам в пункт А заводят 3 морковки, а по пятницам в пункт Б 6 морковок. Итогом должна быть прибыль с морковок за месяц.

[@user-fw8uy2hg3p]

здравствуйте! спасибо большое за такую ценную информацию! пыталась повторить то, что вы делаете в видео-уроке, поменяла названия колонок (использовать верхнюю строку), но при создании сводной там, где у вас в Полях сводной таблицы наименования колонок, у меня почему-то оказались старые названия, типа Column2, Column10 и т.д. Почему так могло произойти?

[@planetaexcel]

А сводную обновляли?

[@Adomich_Aleksandr]

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

[@MeAndWimp]

Сделал сводную первым способом, но не обновляются данные сводной после удаления определенных строк в исходной таблице. Этих строк больше нет, но в редакторе power query они остались. То же самое с изменением некоторых значений. Почему такое может быть?

[@user-vt3fl7rg3k]

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

[@denissevalnev8077]

Здравствуйте Николай, подскажите как можно собрать в один файл данные находящиеся в нескольких файлах и с разными форматами (склеить)? Спасибо.

[@planetaexcel]

Я бы копал в сторону Power Query или макросов.

[@user-yf3ru7fi4e]

Добрый день, Николай. Нужно сделать сводную из 40 таблиц с одинаковой структурой/шапками, но в каждой таблице несколько сотен тысяч строк. В модель данные собрал через Quert, но между листами данные не связываются, так как Each column contains duplicate values. Подходит ли для моей цели PowerPivot?

[@natalytataurova9678]

Здравствуйте, Николай. Огромное спасибо за полезные уроки. Позвольте вопрос: создаю по Вашему способу сводную таблицу через внешнее подключение . Почему-то в сводной итоговые суммы все продублированы - умножаются на два. Что может быть за ошибка?

[@Fluid20092012]

Добрый день. Наталья, у меня аналогичная проблема. Вы нашли решение?

[@natakarachevtseva9800]

@@Fluid20092012 честно говоря - нет. Не удалось понять в чем глюк

[@user-oc6vs7cl9i]

здравствуйте, у меня исчезло параметры запросов правый столбец. Не подскажете как заново поставить? или как настроить Повер Куери?

[@heroskillspowers]

Приветствую, Николай. В Экселе 2015 (версия для Мака) отсутствует возможность создания правила условного форматирования. Подскажите как можно ее добавить?

[@planetaexcel]

Обновиться до последней версии Office 2016 - она почти не отличается по возможностям от версии для Windows.

[@inesatananyan1191]

👍👏💕

[@user-ff6lz7kk1g]

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

[@user-dh2vc1pp7v]

Николай, добрый день. Реализовал способ номер 1. Сборка создалась, сводная таблица построена, все работает. Но отсутствует обновление после изменений данных в источнике при нажатии кнопки "Обновить все". При этом если закрыть файл и открыть повторно, то данные обновляются. Что некорректно сделала? Как настроить обновление по кнопке. Заранее благодарю за ответ.

[@eswcpoker6562]

А можно ли все тоже самое провести только в онлайн экселе на примере гугл таблиц ? Насколько хватило моего внимания повер квери в онлайне нет .

[@planetaexcel]

Точно нет. Гугл-таблицы и Excel - разные вещи :) И ничего похожего на Power Query в Google-таблицах нет.

[@Rice0987]

Что за непорядок? ГДЕ мой лайк?! Исправил! :)

[@user-fc5kk1cj6c]

Николай добрый вечер, я использую Excel на Маке, а тут Power Query очень урезан, я не могу получить данные из других таблиц, что посоветуете?

[@Albertus99]

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

[@user-sf1zg4tk3k]

Николай, это только у меня проблема с доступом к вашему сайту? Не удается получить доступ к сайту Веб-страница по адресу www.planetaexcel.ru, возможно, временно недоступна или постоянно перемещена по новому адресу. ERR_CONNECTION_ABORTED

[@planetaexcel]

Обновляли движок сайта - с полчаса всё лежало. Вы как раз на этот интервал попали, видимо :)

[@user-tp1jt1gv6x]

Добрый день, есть вопросы по уроку, где их можно задать?

[@plushkinaamira]

Добрый день, спасибо за видео. Скажите, а если у меня на каждом листе по миллиону строк с данными. А мне нужно их на один лист собрать и сделать из них один пивот, это возможно?

[@plushkinaamira]

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

[@planetaexcel]

Это легко делается через Power Query - можно глянуть для начала www.planetaexcel.ru/techniques/24/7453/

[@user-pz1nl3be1r]

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

[@saleviks5332]

Добрый день. Спасибо большое. Очень интересная реализация. У меня вопрос. Не могу найти как реализовать следующую задачу? Есть общая таблица - список оборудования фирмы с оценочной стоимостью каждой из позиций. В этой таблице есть позиции очень разной стоимости. Задача: распределить данные с общей таблицы в три другие списка, с приблизительно одинаковой итоговой стоимостью всех включенных в список позиций. Требования к спискам: все три списка должны иметь приблизительно, насколько возможно, одинаковую итоговую стоимость. И еще очень важный момент, каждый из списков должен состоять из приблизительно одинакового количества позиций (это нужно для того чтобы не получилось так, что в одном списке будет всего 4 позиции, но очень дорогие, а в другом 50 позиций, но очень дешевые). Нужно чтобы в новых списках была равномерность по количеству, стоимости отдельных позиций и общей стоимости. Очень нужна ваша помощь.

[@planetaexcel]

Копайте в сторону надстройки Поиск решения (Solver) - это уже решение задачи оптимизации, по сути.

[@saleviks5332]

@@planetaexcel спасибо буду пробовать

[@mayil66]

Огромное спасибо Вам! Есть у меня такой вопрос. Можно ли антологичным способом собрать данные не из разных листьев а из разных КНИГ? Спасибо вам еще раз.

[@planetaexcel]

Да, можно с помощью Power Query. См. kzbin.info/www/bejne/d5nEf6lpfs6macU

[@user-cq3kf7ul2x]

Здравствуйте. Подскажите, если таблицы имеют разные заголовки, соответственно и данные, только некоторые данные одинаковые, то возможно их собрать в одну? Или это не реально?

[@planetaexcel]

Через Power Query можно легко собрать таблицы с разными заголовками - без проблем.

[@user-yh4kl8oc9r]

Подскажите пожалуйста, делаю макросом и не цепляет все строки из листов, в чем может быть проблема? Имена проверял... а первым способом не хватает памяти...(

[@comeasyouare8439]

Добрый день! А что, если у меня есть 10 одинаковых отчётов менеджеров, и мне нужно сделать сводную по их отчётам, те данные я собираю не просто с разных листов, а с разных листов и разных книг? Например, чтобы посмотреть ПДС на неделю или дебеторку по статусу заказа. Каким методом это можно сделать, пока не очень понимаю! Спасибо

[@planetaexcel]

Собрать эти данные из всех 10 файлов с помощью Power Query, а потом сделать по ним сводную ;)

[@ppr8117]

PQ рулит!!!

[@vadymand4203]

Здавствуйте, подскажите как слово например красный поменять на число 1 и слово синий изменить на 2, пол дня уже вожусь))

[@planetaexcel]

=ЕСЛИ(A1="красный";1;ЕСЛИ(A1="синий";2;0))

[@user-cv4qz5jz6v]

Николай, подскажите, а как быть, если происходит урезание данных, т.к. очень много строк?

[@Youriy2010]

Сохраняйте их в запрос, а не выгружайте на лист.

[@user-oy5wl6ne8o]

Добрый день! Подскажите: почему то не удаляется строка "навигация" из панели "примененные шаги". 4 минута на видео.

[@planetaexcel]

Нужно обновить Excel - у вас старая версия, судя по всему.

[@Svetlana-yd6hl]

Как это сделать в некоммерческой версии?

[@user-gv3nn4vq6s]

Николай, добрый вечер. При создании сводной таблицы по нескольким диапазонам данных при помощи Power Query , в поле сводной таблицы "Значения" по умолчанию подтягивается "количество по полю". При изменении в параметрах на "сумму по полю" и на изменении на числовой формат значения сумм не подтягиваются (встают нули). Скажите, пожалуйста, в чем ошибка? Заранее спасибо

[@user-gv3nn4vq6s]

Я создала другой файл с тремя листами и таблицами. Все получилось, cчитается сумма по полю Значения. Что нужно преобразовать в вашем исходном файле? В столбце "Прибыль" стоит формула, убила, вставила как значения, и поменяла на формат числовой все равно считает "количество" в поле Значения.

[@planetaexcel]

А формат данных в Power Query для этого столбца задали числовой или стоит формат по умолчанию "ABC123"?

[@user-wl8cw4fm8z]

@@user-gv3nn4vq6s надо изменить формат ячеек с суммами, в файле применен (#'##0,00"р.") и значения преобразовываются как текст, поэтому не может их суммировать

[@user-wl8cw4fm8z]

@@planetaexcel Николай, приветствую, пыталась преобразовать формат ячеек "#'##0,00"р." когда файл источника (.xls) в десятичное значение. Получилось только в два шага сделать - Замененное значение (меняем "р." на ""), Измененный тип (меняем type text на number). Если файл источник с поддержкой макросов (.xlsm), то формат ячеек "#'##0,00"р." преобразовываются как type number по умолчанию. Фишку не поняла😄- макросы что ли преобразовывают в Power Query?

[@ALFERIUSALFERIUS]

Как объединить в одну таблицу не данные на листах одной книги, а данные с одного листа из разных книг?

[@ALFERIUSALFERIUS]

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

[@thghtfl]

Николай, по миллиону строк с листа квери потянет?

[@igorzinenko4182]

по идее потянет, главное результат придется поместить нажав галочку "добавить эти данные в модель данных", так как больше чем миллион с небольшим строк загрузить в лист excel нельзя

[@user-mx4kl3fz7t]

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

[@planetaexcel]

Это лучше уже не в Power Query, а через Power Pivot решать - он с большими объемами данных работает гораздо быстрее, чем Excel и PQ.

[@7en784]

В первый раз получилось , во второй раз проделал то же самое на том же листе скачанного с примера и выкидывает ошибку *столбец дата, таблицы не найден! * все вроде заработало, но теперь когда кидаешь например продажи он показывает кол-чество, если переводишь в сумму , то показывает все нули((

[@annafirsova3101]

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

[@Nikolay_ZNV]

странно, но в моём интерфейсе 2013 нет такой опции "получить данные", есть только "из других источников"(((

[@svetlanas9394]

В моём тоже. Как Вы вышли из ситауции?

[@user-wl8cw4fm8z]

@@svetlanas9394 Тоже искала)). Создать запрос - Из файла - Из книги

[@ALFERIUSALFERIUS]

А что, только у меня нет кнопки "из файла" - "из книги Эксель"? Офис 2013

[@ALFERIUSALFERIUS]

Ага, в 2013 офисе надо отдельно установить

[@user-xq3qy4qv5z]

А почему нельзя через модель данных это сделать?

[@Youriy2010]

Модель данных нужно использовать тогда, когда требуется соединить таблицу фактов и справочник.

[@user-qn5by5iv7u]

2022

[@user-xp4zp3cm6b]

Способы имеют место быть, только какой в них смысл если гораздо проще все сделать просто скопировав все на один лист и построив обычную сводную? Power query может пригодиться разве что если кол-во строк будет больше очень большим и Эксель будет плохо тянуть такое количество.

[@Youriy2010]

Ваш вопрос звучит так: какой смысл в операции умножения, когда можно выполнить операцию сложения n раз? :))

[@0lga0lga27]

Николай, присоединяюсь ко всем благодарностям и прошу объяснить ситуацию, возникшую в моём случае при применении Ваших инструкции на 6:25-6:30 минутах этого видео, а именно: при попытке удалить лишние заголовки (шапки из всех листов) я воспользовалась фильтром по одному из столбцов, получила честное предупреждение от Query "Список может быть незавершённым", нажала Загрузить ещё и... появилось предупреждение "Достигнуто ограничение в 1000 значений" и кроме того, что я не увидела название из заголовков (шапок), появилось опасение, что Query не все собрал. Заранее признательна за ответ.

[@user-vz6pl5mk2z]

По инжерным расчётам не годится. Годится для товаров магазинов.... фруктов... М-да

[@planetaexcel]

Почему? Какая разница какие данные?

[@user-ly6cm2jd5u]

Через старый мастер по другому надо делать.

[@planetaexcel]

Сводная, сделанная через старый Мастер консолидации, будет очень ограниченная по возможностям. С обычной сводной - не сравнить.

[@user-ly6cm2jd5u]

@@planetaexcel Ну да, выч.поля и элементы не сделаешь, но и того безобразия которое у вас получилась не будет.До появления PQ только так и делал.

[@alexvap3280]

Я не большой спец в Excel, поэтому если задам глупый вопрос, не бросайте в меня камни ))) А что, консолидация вместо запроса, для этого не подошла бы?

[@antonnovichkov2207]

нет

[@planetaexcel]

Консолидация только суммирует совпадения, а сводная позволяет строить любые отчеты по любым параметрам и срезам. Это на порядок больше свободы и возможностей :)

[@alexvap3280]

+ Nikolay Pavlov Спасибо за ответ

[@user-sj8gh2cs8i]

Нет*

[@user-op8dp5pl5g]

Слово эксел ухо режет. Как у джигитов шэст. Мягкий знак то в школе изучали?

[@planetaexcel]

Изучал. А ещё изучал английский ;)

[@ab2rahim254]

Причем тут мягкий знак тут же не урок русского языка

[@MaksymKMS]

Добрый день. Почему у меня не получается получить сумму, а только кол-во елементов. В исходной таблице четко стоит значение данных "число", а когда я получаю сводную таблицу, то мне пишет, что там текст. А точнее, почему в сам Power Query данные приходят как текст? потому что приходится вручную менять на число И еще один вопрос. Можете подсказать как сделать так, что бы в конце сводной таблицы выводился на общий итог, к примеру, что бы писало в конце Альфа - 42039607,91