Сводная таблица по нескольким диапазонам данных
Вставка
- Опубліковано 4 сер 2018
- 3 способа сделать сводную в Excel по нескольким таблицам исходных данных. Скачать пример www.planetaexcel.ru/technique...
Заходите в гости ↓↓↓
Twitter / planeta_excel Facebook / planetaexcel Instagram / planetaexcel Мои книги planetaexcel.ru/books/ Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Данное видео координально изменило работу всего нашего отдела. Теперь на отчёт, который составляла коллега неделю, я трачу всего пару часов! Спасибо вам огромное за такие полезные видео!!!
Николай, здравствуйте. Большой респект вам за видео, кратко, понятно, доступно, просто супер.
Николай, здоровья и процветания Вам, спасибо за уроки...благодаря Вашим стараниям я экономлю тьму времени
Доброго времени. Огромное желание освоить таблицы уже долго сидело в голове, благодаря вам оно начало воплощаться, все доступно и понятно рассказано, Благодарю
👍
Николай, спасибо! Всё так понятно и позитивно!!!
Оптимизм - наш долг! :)
Спасибо за это видео. Очень помогло. Не мог файл сделать из-за слишком большого количества строк, а ролик с power query прям спас ситуацию ))
Спасибо большое! Шикарный материал!
Спасибо Николай! Вы гений!
Супер способ! у меня были листы по 900к строк из который надо было собрать один сводник, по первому способу прекрасно получилось за 10 минут!! спасибо огромное!!!!!!!!!!!!!!!!!!!!!!!! а то страшно подумать, что за садо-мазо пришлось бы делать без этого
Николай! Спасибо за Способ 1 - Power Query, все остальные на мой взгляд нормально устарели после появления Power Query, но знать что раньше эту задачу решали способами 2 или 3 полезно... это история развития Excel...
В третьем варианте, удобство в том, что если щелкнуть на пересечении итогов, то таблица разложиться в плоскую и одномерную (UNPIVOT). Как раз искал где этот мастер, а он вот он где. Как говорил один клиент - спасибо что Вы есть! :)
Николай спасибо большое! Благодарю
Очень помогли, благодарна от всей души.
Большое спасибо за видеоурок!
Большое спасибо Николай
У автора очень приятный слог
Спасибо! Это то, что нужно!)
Класс! Будем пробовать)
Спасибо большущее за Вашу работу Николай.
Нужна подробная инструкция по созданию стилей сводной таблицы.
Все по полочка, спасибо!
Like!
Спасибо за ролик!
Большое спасибо! все получилось. Но изначально нужно скачать надстройку для excel
Спасибо за видео!!!!!
Николай, добрый день и спасибо за данный урок!
Не могли бы вы подсказать мне аналогичные UA-cam проекты, в которых обучают приёмам работы с Word, PowerPoint и другим ПО MS Office?
Эх, как мне нужно такое качественное видео по МАС. Где найти? Смотрю эти видео (все!)) из-за качества. Получаю удовольствие)
По Excel для Mac или просто по работе на Mac?
Nikolay Pavlov Хотя бы по таблицам Numbers. Я бы рада была. Некоторых опций сильно не хватает и я не могу поверить что это и не заложено. Например, шикарная фишка экселя по ссылкам на другие ячейки и другие листы отсутствует, насколько я понимаю на МАСе. Как жить?))))
Numbers - это прошлый век. Поставьте себе хотя бы Microsoft Office for Mac 2016 - и будет вам счастье :)
Nikolay Pavlov :(
@@planetaexcel по мне так прошлый век Ексель. Numbers в плане визуализации, положит Ексель на лопатки. Я свои техзадания и расчёты выполняем в нём, хотя начинал в Екселе, который знаю очень хорошо. Десяток таблиц на одном листе, в перемешку с графиками, схемами, зарисовками, делают мой продукт очень дорогим. Смотря какие задачи решаем
Николай спасибо
Огонь!
прикольно. спасибо.
Привет спасибо большое
Здравствуйте. Интересно, можно ли в PQ (или Excel) собрать несколько таблиц в одну, и чтобы собранная таблица была обратно совместима с раздельными.
Скажем изменил данные в собранной таблице и данные появились в раздельной таблице (конечно речь идёт только о редактировании данных, а не добавлении новых).
Здравствуйте! Подскажите пожалуйста как воспользоваться формулами которые Excel предлагает списком ниже строки формул?
Классно!
Есть ли у вас видео об обединении однотипних файлов, в каждом из которых находятся разные (не однотипные) вкладки.
Возможно ли обединить все в один файл но з разбивкой по вкладкам?
Николай, приветствую!
Создал подключение по первому варианту: сделал книгу в которой в двух вкладках (11 и 12 декабря) разместил все данные. Способ сработал. Добавил третью вкладку 10 декабря с данными и обновил подключение. Как итог: в книге со сводной таблицей появилось подключение вкладки 10 декабря, но без данных. Что пошло не так?
Николай, спасибо за подробное описание процесса. Подскажи пожалуйста, можно ли к сделанной сводной, добавить срез, которым можно выбирать, чтобы сводная отображала данные по закупке, продаже, прибыли или наценке?
Не можно, а нужно.
Николай, спасибо за ваши супер полезные видео! Помогают экономить массу времени.
Подскажите, пожалуйста, как сделать сводную из двух совершенно разных листов, при том что шапки, данные и размеры разные. Есть только общий параметр - товар?
Можно загрузить обе таблицы в Модель данных (Power Pivot), настроить связи и построить потом обычную сводную, как будто это одна таблица - см. www.planetaexcel.ru/techniques/8/1233/
Здравствуйте, подскажите пожалуйста, а как в облаке в екселе сделать отдельную вкладку которая будет собирать автоматически информацию по предыдцщим вкладкам( наименование товара , остаток товара, цена)
хорошие уроки
Спасибо за пособие, помогло, я применил источник данных из онлайн google таблиц, вроде разобрался! У гугла тоже есть сводная, но локализация на англ. и функционал не понятен.
Добрый день, Николай. Нужно сделать сводную из 40 таблиц с одинаковой структурой/шапками, но в каждой таблице несколько сотен тысяч строк. В модель данные собрал через Quert, но между листами данные не связываются, так как Each column contains duplicate values. Подходит ли для моей цели PowerPivot?
Сделал сводную первым способом, но не обновляются данные сводной после удаления определенных строк в исходной таблице. Этих строк больше нет, но в редакторе power query они остались. То же самое с изменением некоторых значений. Почему такое может быть?
а возможно создать таблицу, для получения суммы прибыли за месяц по разным предприятиям, с учётом выполнения работы в определённые дни недели, и с учётом разных объёмов работ? Пример: по понедельникам и средам в пункт А заводят 3 морковки, а по пятницам в пункт Б 6 морковок. Итогом должна быть прибыль с морковок за месяц.
здравствуйте! спасибо большое за такую ценную информацию!
пыталась повторить то, что вы делаете в видео-уроке, поменяла названия колонок (использовать верхнюю строку), но при создании сводной там, где у вас в Полях сводной таблицы наименования колонок, у меня почему-то оказались старые названия, типа Column2, Column10 и т.д. Почему так могло произойти?
А сводную обновляли?
Здравствуйте Николай, подскажите как можно собрать в один файл данные находящиеся в нескольких файлах и с разными форматами (склеить)? Спасибо.
Я бы копал в сторону Power Query или макросов.
Здравствуйте! Подскажите, можно ли связать в одну сводную таблицу столбцы с разных листов или разных именованных диапазонов? На первом листе столбец дата, на втором продажи, а в третьем город и всё это соединить в одну сводную таблицу.
А в чем проблема объединить эти столбцы на новом листе в новую таблицу и по ней уже построить сводную? Или сделать это в редакторе Power Query?
Николай, добрый день!
Подскажите, пробую сделать на вашем примере все то же самое, но у меня в сводной не считает сумму по полю продажа, количество по этому же полю считает а вот в сумму выходят нули, в чем может быть причина? если что офис 2016, ну и окна по созданию запроса у меня немного отличаются от ваших...
Спасибо
Доброе время суток, при создании свода за два месяца при выборе вверхнем углу свода фильтра по виду и другое то пропадает один месяц т.е второй в таблице. Например сентябрь и октябрь, октябрь скрывается, что делать
Приветствую, Николай. В Экселе 2015 (версия для Мака) отсутствует возможность создания правила условного форматирования. Подскажите как можно ее добавить?
Обновиться до последней версии Office 2016 - она почти не отличается по возможностям от версии для Windows.
Отлично, других комментариев нет.
Добрый вечер)
Спасибо Николай за урок Сводная таблица из несколько диапазонов данных)Хотела уточнить если не удаляется шаг навигация(нету даже хрестика),как быть?
Как его можно удалить?
Спасибо)
Извените как Вы решили проблему с навигацией?
Николай, добрый день. Реализовал способ номер 1. Сборка создалась, сводная таблица построена, все работает. Но отсутствует обновление после изменений данных в источнике при нажатии кнопки "Обновить все". При этом если закрыть файл и открыть повторно, то данные обновляются. Что некорректно сделала? Как настроить обновление по кнопке. Заранее благодарю за ответ.
здравствуйте, у меня исчезло параметры запросов правый столбец. Не подскажете как заново поставить? или как настроить Повер Куери?
Добрый день, Николай! Спасибо за материал. На 4:00 вы отключаете навигацию, но у меня не получается повторить это действие за вами. еще раз спасибо за обучение и, пожалуйста, подскажите с чем это может быть связано.
Аналогично. Никак не могу удалить пункт "Навигация". В итоге дальше выдает ошибку.
я также не могу отключить кнопку навигация (((
спасибо
Здравствуйте, Николай. Огромное спасибо за полезные уроки. Позвольте вопрос: создаю по Вашему способу сводную таблицу через внешнее подключение . Почему-то в сводной итоговые суммы все продублированы - умножаются на два. Что может быть за ошибка?
Добрый день. Наталья, у меня аналогичная проблема. Вы нашли решение?
@@Fluid20092012 честно говоря - нет. Не удалось понять в чем глюк
Николай добрый вечер, я использую Excel на Маке, а тут Power Query очень урезан, я не могу получить данные из других таблиц, что посоветуете?
Добрый день, есть вопросы по уроку, где их можно задать?
1:56 Power Query
9:48 макрос с SQL командой UNION
12:46
👍👏💕
Добрый день Николай! очень простой вопрос для вас, не могу найти ответ.В справочнике умной таблицы некоторые позиции стали неактуальными. в самом справочнике я их удалила, а при на боре данных в выпадающем списке они не уходят. где то есть настройки, ноя их не найду. помогите пож, разобраться.
Николай, это только у меня проблема с доступом к вашему сайту?
Не удается получить доступ к сайту
Веб-страница по адресу www.planetaexcel.ru, возможно, временно недоступна или постоянно перемещена по новому адресу.
ERR_CONNECTION_ABORTED
Обновляли движок сайта - с полчаса всё лежало. Вы как раз на этот интервал попали, видимо :)
У меня квери распознал числа как текст и пришлось добавлять шаг убрать рубли и преобразовать в цисло.
в ролике этого нет видимо я что-то упустил.
Николай скажите пожалуйста что и где я мог упустить?
спасибо.
А можно ли все тоже самое провести только в онлайн экселе на примере гугл таблиц ? Насколько хватило моего внимания повер квери в онлайне нет .
Точно нет. Гугл-таблицы и Excel - разные вещи :) И ничего похожего на Power Query в Google-таблицах нет.
искал долго в инете и не нашёл.
но нужна
нужна таблица терминов в эксель. очень нужна.
например макросы и диапазон данных.и не терминами а обычным понятным языом
Добрый день! А что, если у меня есть 10 одинаковых отчётов менеджеров, и мне нужно сделать сводную по их отчётам, те данные я собираю не просто с разных листов, а с разных листов и разных книг? Например, чтобы посмотреть ПДС на неделю или дебеторку по статусу заказа. Каким методом это можно сделать, пока не очень понимаю! Спасибо
Собрать эти данные из всех 10 файлов с помощью Power Query, а потом сделать по ним сводную ;)
Здравствуйте. Подскажите, если таблицы имеют разные заголовки, соответственно и данные, только некоторые данные одинаковые, то возможно их собрать в одну? Или это не реально?
Через Power Query можно легко собрать таблицы с разными заголовками - без проблем.
Подскажите пожалуйста, делаю макросом и не цепляет все строки из листов, в чем может быть проблема? Имена проверял... а первым способом не хватает памяти...(
Добрый день! Подскажите: почему то не удаляется строка "навигация" из панели "примененные шаги". 4 минута на видео.
Нужно обновить Excel - у вас старая версия, судя по всему.
Добрый день, спасибо за видео. Скажите, а если у меня на каждом листе по миллиону строк с данными. А мне нужно их на один лист собрать и сделать из них один пивот, это возможно?
Пожалуйста, умоляю, можно с кем нибудь проконсультироваться по данному вопросу? За деньги, конечно
Это легко делается через Power Query - можно глянуть для начала www.planetaexcel.ru/techniques/24/7453/
Добрый день. Спасибо большое. Очень интересная реализация. У меня вопрос. Не могу найти как реализовать следующую задачу?
Есть общая таблица - список оборудования фирмы с оценочной стоимостью каждой из позиций. В этой таблице есть позиции очень разной стоимости. Задача: распределить данные с общей таблицы в три другие списка, с приблизительно одинаковой итоговой стоимостью всех включенных в список позиций.
Требования к спискам: все три списка должны иметь приблизительно, насколько возможно, одинаковую итоговую стоимость.
И еще очень важный момент, каждый из списков должен состоять из приблизительно одинакового количества позиций (это нужно для того чтобы не получилось так, что в одном списке будет всего 4 позиции, но очень дорогие, а в другом 50 позиций, но очень дешевые). Нужно чтобы в новых списках была равномерность по количеству, стоимости отдельных позиций и общей стоимости.
Очень нужна ваша помощь.
Копайте в сторону надстройки Поиск решения (Solver) - это уже решение задачи оптимизации, по сути.
@@planetaexcel спасибо буду пробовать
Николай, подскажите, а как быть, если происходит урезание данных, т.к. очень много строк?
Сохраняйте их в запрос, а не выгружайте на лист.
Что за непорядок? ГДЕ мой лайк?! Исправил! :)
Огромное спасибо Вам!
Есть у меня такой вопрос. Можно ли антологичным способом собрать данные не из разных листьев а из разных КНИГ? Спасибо вам еще раз.
Да, можно с помощью Power Query. См. ua-cam.com/video/AhaNv3Iis3c/v-deo.html
Здавствуйте, подскажите как слово например красный поменять на число 1 и слово синий изменить на 2, пол дня уже вожусь))
=ЕСЛИ(A1="красный";1;ЕСЛИ(A1="синий";2;0))
Николай, добрый вечер. При создании сводной таблицы по нескольким диапазонам данных при помощи Power Query , в поле сводной таблицы "Значения" по умолчанию подтягивается "количество по полю". При изменении в параметрах на "сумму по полю" и на изменении на числовой формат значения сумм не подтягиваются (встают нули). Скажите, пожалуйста, в чем ошибка? Заранее спасибо
Я создала другой файл с тремя листами и таблицами. Все получилось, cчитается сумма по полю Значения. Что нужно преобразовать в вашем исходном файле? В столбце "Прибыль" стоит формула, убила, вставила как значения, и поменяла на формат числовой все равно считает "количество" в поле Значения.
А формат данных в Power Query для этого столбца задали числовой или стоит формат по умолчанию "ABC123"?
@@user-gv3nn4vq6s надо изменить формат ячеек с суммами, в файле применен (#'##0,00"р.") и значения преобразовываются как текст, поэтому не может их суммировать
@@planetaexcel Николай, приветствую, пыталась преобразовать формат ячеек "#'##0,00"р." когда файл источника (.xls) в десятичное значение. Получилось только в два шага сделать - Замененное значение (меняем "р." на ""), Измененный тип (меняем type text на number). Если файл источник с поддержкой макросов (.xlsm), то формат ячеек "#'##0,00"р." преобразовываются как type number по умолчанию. Фишку не поняла😄- макросы что ли преобразовывают в Power Query?
PQ рулит!!!
Николай, по миллиону строк с листа квери потянет?
по идее потянет, главное результат придется поместить нажав галочку "добавить эти данные в модель данных", так как больше чем миллион с небольшим строк загрузить в лист excel нельзя
Добрый день, Николай! Не по теме, но спрошу, подскажите, пожалуйста, есть график, как с него снять промежуточные значения? Данные не в ячейке, график с экстраполяцией данных
Это лучше уже не в Power Query, а через Power Pivot решать - он с большими объемами данных работает гораздо быстрее, чем Excel и PQ.
Как это сделать в некоммерческой версии?
Как объединить в одну таблицу не данные на листах одной книги, а данные с одного листа из разных книг?
Сам спросил, сам ответил.
Добавляем файлы в повер квери поочереди, а потом используем кнопку добавить запросы в редакторе повер квери. После этого они выполняются как один запрос.
В первый раз получилось , во второй раз проделал то же самое на том же листе скачанного с примера и выкидывает ошибку *столбец дата, таблицы не найден! * все вроде заработало, но теперь когда кидаешь например продажи он показывает кол-чество, если переводишь в сумму , то показывает все нули((
У меня сразу выкидывал ту же ошибку. Но на этапе вставки сводной таблицы после проставления галки в поле Добавить эти данные в модель данных - все прошло без ошибок. Хотя в видео это поле пустое
странно, но в моём интерфейсе 2013 нет такой опции "получить данные", есть только "из других источников"(((
В моём тоже. Как Вы вышли из ситауции?
@@svetlanas9394 Тоже искала)). Создать запрос - Из файла - Из книги
А почему нельзя через модель данных это сделать?
Модель данных нужно использовать тогда, когда требуется соединить таблицу фактов и справочник.
А что, только у меня нет кнопки "из файла" - "из книги Эксель"?
Офис 2013
Ага, в 2013 офисе надо отдельно установить
2022
Способы имеют место быть, только какой в них смысл если гораздо проще все сделать просто скопировав все на один лист и построив обычную сводную? Power query может пригодиться разве что если кол-во строк будет больше очень большим и Эксель будет плохо тянуть такое количество.
Ваш вопрос звучит так: какой смысл в операции умножения, когда можно выполнить операцию сложения n раз? :))
Николай, присоединяюсь ко всем благодарностям и прошу объяснить ситуацию, возникшую в моём случае при применении Ваших инструкции на 6:25-6:30 минутах этого видео, а именно: при попытке удалить лишние заголовки (шапки из всех листов) я воспользовалась фильтром по одному из столбцов, получила честное предупреждение от Query "Список может быть незавершённым", нажала Загрузить ещё и... появилось предупреждение "Достигнуто ограничение в 1000 значений" и кроме того, что я не увидела название из заголовков (шапок), появилось опасение, что Query не все собрал. Заранее признательна за ответ.
По инжерным расчётам не годится. Годится для товаров магазинов.... фруктов... М-да
Почему? Какая разница какие данные?
Я не большой спец в Excel, поэтому если задам глупый вопрос, не бросайте в меня камни ))) А что, консолидация вместо запроса, для этого не подошла бы?
нет
Консолидация только суммирует совпадения, а сводная позволяет строить любые отчеты по любым параметрам и срезам. Это на порядок больше свободы и возможностей :)
+ Nikolay Pavlov Спасибо за ответ
Нет*
Через старый мастер по другому надо делать.
Сводная, сделанная через старый Мастер консолидации, будет очень ограниченная по возможностям. С обычной сводной - не сравнить.
@@planetaexcel Ну да, выч.поля и элементы не сделаешь, но и того безобразия которое у вас получилась не будет.До появления PQ только так и делал.
Слово эксел ухо режет. Как у джигитов шэст. Мягкий знак то в школе изучали?
Изучал. А ещё изучал английский ;)
Причем тут мягкий знак тут же не урок русского языка
Добрый день. Почему у меня не получается получить сумму, а только кол-во елементов. В исходной таблице четко стоит значение данных "число", а когда я получаю сводную таблицу, то мне пишет, что там текст.
А точнее, почему в сам Power Query данные приходят как текст? потому что приходится вручную менять на число
И еще один вопрос. Можете подсказать как сделать так, что бы в конце сводной таблицы выводился на общий итог, к примеру, что бы писало в конце Альфа - 42039607,91