Переводчик Д. Строганов

 

Томас Фрагейл

Магия Excel для непрограммистов. Сводные таблицы, Power Query, дашборды и многое другое. — СПб.: Питер, 2025.

 

ISBN 978-5-4461-4215-6

© ООО Издательство "Питер", 2025

 

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

 

Посвящается самому сильному, смелому и отважному человеку, которого я когда-либо встречал, — Жозефине Баста. Летай свободно, сейчас и всегда. С большой любовью к тебе.

Благодарности

Мне выпала большая удача поработать с высокопрофессиональными сотрудниками Wiley, которые очень помогли мне в написании этой книги.

Джим Минател (Jim Minatel) из издательства John Wiley&Sons начал работать со мной летом 2023 года. Джим, большое спасибо вам и вашей ассистентке Саре Дайхман (Sara Deichman) за сотрудничество. Я также высоко ценю помощь ваших коллег — редакторов Брэда Джонса (Brad Jones), Джойс Нилсен (Joyce Neilsen), Магеша Элангована (Magesh Elangovan), Павитры Санкара (Pavithra Sankar), Сатиша Гоуришанкара (Satish Gowrishankar) и всех остальных сотрудников Wiley. Спасибо вам всем за то, что помогли сделать эту книгу гораздо лучше. Я бесконечно признателен абсолютно всем работникам Wiley. Отдельное спасибо Брэду и Джойс, которые терпели все мои причуды и упрямство, деликатно направляя меня к лучшим вариантам всех частей данной книги.

Я также высоко ценю участие моего хорошего друга — Медии Болдуин (Media Baldwin), которая оказала мне любезность и написала предисловие к этой книге, несмотря на ее серьезнейшую занятость.

Я признателен всем членам моей семьи — Мэри Кавистон (Mary Caviston), Винсенту Фрагейлу (Vincent Fragale), Жанне Келлер (Jeanne Keller), Мелиссе Кавистон (Melissa Caviston), Джиму и Кэрол Брилл (Jim и Carol Brill), Джону Кавистону (John Caviston), Шону, Эмили, Тедди, Луису и Роуз Кавистон (Sean, Emily, Teddy, Louis и Rose Caviston) — я вас всех очень люблю.

Всем остальным моим друзьям и близким — огромная благодарность.

Большую роль сыграли те компьютерные курсы, которые я прошел в Цент­ральной средней школе в Филадельфии (класс 241) и в университете Ла Саль в Филадельфии в конце 70-х и в 80-х годах. Меня сразу захватил этот предмет, целиком и полностью.

Спасибо всем вам — читателям этой книги. Я ценю каждого из вас.

Том Фрагейл

Об авторе

Томас Фрагейл — сертифицированный инструктор Microsoft с более чем 40-летним опытом работы в сфере ИТ, из которых он 24 года занимается обучением сотрудников различных корпораций работе с продуктами Microsoft. За это время в ходе проведенных им онлайн-вебинаров, очных семинаров и выездных тренингов он обучил более 50 000 человек. Среди его клиентов — многие компании из списка Fortune 500, государственные учреждения, военные подразделения, а также компании большого и малого бизнеса из многих отраслей — таких как страховое и банковское дело, промышленное производство, фармацевтика, образование, розничная торговля и т.д. В сферу его компетенции входят такие темы, как работа с Access, Excel, Word, PowerPoint, Outlook, Crystal Reports, SQL Server, Power BI, Teams, Visio, QuickBooks, SharePoint и другими программными продуктами. Он получает удовольствие от того, что помогает людям приобретать необходимые знания в этих областях, и учит их извлекать для себя максимум пользы из работы на компьютере. В 1988 году он окончил Университет Ла Саль в Филадельфии (штат Пенсильвания), получив степень бакалавра в области computer science. В настоящее время он проживает в Черри-Хилл (штат Нью-Джерси) и по-прежнему проводит множество полезных вебинаров и семинаров для людей в Америке и во всем мире, а также является профессором Фордемского университета.

О научном редакторе

Джойс Дж. Нильсен (Joyce J. Nielsen) проработала в издательской отрасли более 30 лет — в качестве автора, научного и ведущего редактора, а также менеджера проектов. До своего прихода в издательский бизнес Джойс была аналитиком в компании Simon Property Group в Индианаполисе. Она получила степень бакалавра в области количественного бизнес-анализа в Школе бизнеса Келли при Индианском университете в Блумингтоне. В настоящее время Джойс проживает в Аризоне.

Предисловие

В 2002 году Стивен Спилберг выпустил фильм «Особое мнение»1. Я завороженно наблюдала за тем, как его главный герой — сыщик — легко, просто прикосновением пальца переносит данные с одного голографического экрана на другой. В фильме такая технологическая возможность используется для того, чтобы помочь вычислить потенциального убийцу еще до того, как тот совершит преступление.

Разумеется, это научная фантастика, но данный эпизод фильма заставил меня задуматься: а могут ли быстро развивающиеся технологии позволять компаниям предотвращать возможные катастрофы действительно до того, как они произойдут?

Я тогда начала свою карьеру преподавателя-инструктора, обучающего пользователей компьютеров различным техническим приемам. По мере того как к моим услугам стали прибегать все больше и больше организаций в США и по всему миру, я поняла, что, хоть овладение навыками работы с программными продуктами и является полезным делом, но оно не решает более серьезной проблемы: достижения понимания того, как нужно принимать — на основе имеющихся данных — такие решения, которые способствуют получению положительных результатов. Понятно, что для этого данные должны быть доступными и актуальными.

В качестве решения я предложила проводить «аудит эффективности», который помогал бы людям по-новому взглянуть на находящуюся в их распоряжении информацию. В рамках такого аудита мы определяли, какие данные важны прямо сейчас, а какие могут оказаться полезными в будущем. Мы находили и устраняли неэффективное дублирование рабочих операций и создавали хранилища данных. Благодаря интерактивным дашбордам пользователи получили доступ к анализу необходимой именно им информации, взятой из общей для всех сокровищницы актуальных данных. Принимать решения стало проще и быстрее, а сами эти решения стали более взвешенными.

Вот тогда-то я впервые встретила Тома Фрагейла. Нас познакомил общий друг, и мы быстро сблизились из-за присущей нам обоим страсти ко всяким техническим «штучкам» и ко всему, что связано с преподаванием. Принять решение о начале сотрудничества с Томом в проектах по обучению и разработке баз данных было легко. Эти деловые отношения переросли в крепкую дружбу, и я с гордостью могу сказать, что она длится уже более двадцати лет.

Когда Том сообщил мне, что ему предложили написать эту книгу, идея мне сразу понравилась.

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

Компания Microsoft предоставляет такие возможности с помощью инструмента сводных таблиц Excel. Microsoft Excel доступен и относительно прост в использовании. Сводные таблицы — это скрытая мощь знакомого всем программного пакета. Если вы знаете, как с ними работать, то сможете быстро анализировать как большие, так и малые массивы данных. Они изначально могут находиться и внутри, и вне Excel. Инструмент сводных таблиц является гибким и интерактивным. С его помощью можно просматривать и перемещать данные и изучать их под разными углами практически так же легко, как это делалось в той сцене из фильма «Особое мнение» (конечно, без использования голографических экранов!).

В книге "Магия Excel для непрограммистов. Сводные таблицы, Power Query, дашборды и многое другое" Том делится своими специальными знаниями в области моделирования и организации данных. Он хорошо знает, как можно использовать этот мощный инструмент. Кроме того, он опытный и квалифицированный преподаватель. Том предусмотрительно включил в книгу множество примеров и ссылки на соответствующие файлы. Скачайте их, чтобы иметь возможность попрактиковаться по ходу изучения каждой главы. Читая книгу, вы методично перейдете от вопроса «как» что-то сделать к объяснению «почему» нужно делать именно так; с вами поделятся самыми лучшими и передовыми способами, методами и приемами работы. В мгновение ока имеющиеся у вас данные поведают вам захватывающую и ранее неизвестную историю.

Я искренне рада, что эта книга попала к вам в руки. Это отличный справочник, к которому вы будете обращаться снова и снова.

Медиа Болдуин, генеральный директор компании «Diversified Seminars»

---

От издательства

При подготовке русскоязычного издания использовалась локализованная версия Microsoft Excel. Все скриншоты и описания элементов интерфейса, а также формул приведены по русской версии.

Примеры файлов MS Excel, на основе которых автор ведет изложение, оставлены на английском языке, но там, где необходимо, в тексте приводится перевод названий столбцов и строк таблиц.

Все файлы примеров вы можете скачать на сайте издательства «Питер» по ссылке: https://www.piter.com/product/magiya-excel-dlya-neprogrammistov-svodnye-tablitsy-power-query-dashbordy-i-mnogoe-drugoe

Ваши замечания, предложения, вопросы отправляйте по адресу comp@piter.com (издательство «Питер», компьютерная редакция).

Мы будем рады узнать ваше мнение!

На веб-сайте издательства www.piter.com вы найдете подробную информацию о наших книгах.

Введение

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

В современном мире нам всем приходится иметь дело с огромными объемами информации из разных источников. Кроме того, наши коллеги, руководители, поставщики и клиенты постоянно требуют от нас всевозможные отчеты и графики, которые строятся на основе этих данных. Они также хотят больше интерактивности, чтобы была возможность менять различные параметры и мгновенно получать необходимые результаты. И кстати, все это нужно им было еще вчера! Отсортировать данные таким-то образом, отфильтровать их как-то по-особенному, показать помесячно и т.д. и т.п. Все это, мягко говоря, непросто и трудоемко. К тому же у вас как у сотрудника есть и другие связанные с вашей работой обязанности.

В Microsoft Excel встроены мощные инструменты, которые помогут вам управлять данными и составлять на их основе разнообразные отчеты в таком виде, как требуется. Используя сводные таблицы, инструменты Power Query и PowerPivot, вы сможете объединять данные из многих источников. Вы также сможете скрывать ту информацию, которая в данный момент не нужна, подготавливать данные, анализировать и обобщать их различными способами, вводить формулы, создавать визуально привлекательные и интерактивные дашборды и в итоге получать реальные результаты на основе имеющейся у вас информации. И главное — все это можно делать, не будучи опытным программистом. В этой книге пошагово объясняется, как использовать все эти инструменты. Все действия проиллюстрированы скриншотами и примерами из реальной жизни.

О чем эта книга?

Сводные таблицы Excel — это изумительный инструмент, который помогает обобщить данные практически как угодно. Я постарался сделать эту книгу как можно более подробной, чтобы вы смогли увидеть неисчерпаемые возможности сводных таблиц и использовать их в своей работе.

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

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

Глава 1. Подготовка данных для сводной таблицы Excel

В этой главе рассказывается о том, как структурировать данные, чтобы на их основе можно было создать сводную таблицу. Вы узнаете, как импортировать данные из других источников, таких как текстовые/CSV-файлы, базы данных Access, веб-сайты, базы данных ODBC и др. Мы также рассмотрим, как подготавливать данные с помощью Power Query.

Глава 2. Обобщение и представление данных с помощью сводной таблицы

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

Глава 3. Вычисления в сводной таблице

В этой главе мы поговорим о том, как в сводной таблице можно использовать разнообразные встроенные математические функции и как создавать собственные функции.

Глава 4. Сортировка и фильтрация данных в сводной таблице

В этой главе описываются способы сортировки и фильтрации в сводной таб­лице, включая срезы и временны́е шкалы, с помощью которых вы сможете получать нужные вам результаты.

Глава 5. Повышение наглядности сводной таблицы с помощью диаграмм

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

Глава 6. Обобщение данных по критериям даты и времени

Из этой главы вы узнаете, как сгруппировать и обобщить данные по году, кварталу, месяцу, дню, часу, минуте, секунде или по любому другому временно́му интервалу. Кроме того, я покажу, как использовать встроенные функции Excel для обобщения данных в сводной таблице по произвольным диапазонам дат и времени.

Глава 7. Создание сводной таблицы из нескольких электронных таблиц

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

Глава 8. Расширение возможностей сводных таблиц с помощью инструмента Power Pivot

В этой главе будут продемонстрированы способы еще большего расширения возможностей моделей данных с помощью инструмента Power Pivot. Также будет показано, как писать формулы на языке DAX (Data Analysis Expression) для сводных таблиц на основе модели данных.

Глава 9. Собираем все воедино: создание дашборда для сводных таблиц

Из этой главы вы узнаете, как с помощью диаграмм, срезов, временны́х шкал, математических формул и элементов управления формами создавать удобные, динамичные, интерактивные дашборды на основе сводных таблиц.

Для кого предназначена эта книга

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

---

Глава 1. Подготовка данных для сводной таблицы Excel

Если у вас, как и у большинства офисных работников, имеются терабайты разного рода данных, стекающихся со всех сторон, то вам наверняка потребуется все это каким-то образом обобщить, структурировать и подготовить так, чтобы можно было делать полезные и значимые выводы. Возможно, эти данные имеют вид бесконечных списков, в которых фиксируются отдельные акты продаж, расходы и выплаты; либо это перечни клиентов, поставщиков, сотрудников, пособий, заказов; возможно, это сведения о складских запасах или каталоги товаров, книг, коллекций; это могут быть бесчисленные множества накладных, счетов-фактур или других всевозможных документов. Кроме того, весьма вероятно, что все эти данные поступают к вам из самых разных источников и такой информационный поток не иссякает.

Когда-то давным-давно, когда я только начинал работать в сфере ИТ, сбор всех данных, а затем создание на их основе разного рода отчетов, графиков и диаграмм было — по большей части — делом команды ИТ-специалистов. В то время в каждом таком случае требовалось писать длинные и сложные программы, которые после своего запуска должны были открывать файлы данных, просматривать поочередно все содержащиеся в них записи, сортировать, фильтровать и, если требовалось, как-то обобщать, а затем, наконец, выдавать запрошенные конечными пользователями отчеты или диаграммы. Такие программы разрабатывались на различных языках программирования: COBOL, Fortran, Basic, Pascal, ColdFusion, VBA, dBase, FoxPro и др. Это был весьма трудоемкий процесс, чреватый ошибками и возникновением многих других проблем.

Для большинства компаний эти времена уже в далеком прошлом. Теперь это ваша обязанность (или ваших коллег и сотрудников) — собирать все данные из разных источников и, проведя соответствующий анализ, извлекать из них какие-то знания. От вас ожидается, что вы будете знать, как сортировать, фильтровать, обобщать данные, составлять диаграммы и отчеты, чтобы на различного рода планерках и совещаниях представлять какие-то значимые выводы, которые следуют из этих данных. Ах да, кстати, ведь следующее такое совещание назначено на сегодня после обеда! Но вы же справитесь? Ведь это всего лишь рабочая рутина! Правда, от нее зависит ваша карьера, а может быть, и ваша дальнейшая жизнь…

Что же делать?

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

Что такое данные

Для целей этой книги понятие «данные» можно определить как список из строк, содержащих некие сведения или транзакции, которые каким-либо образом связаны между собой. Наборы данных могут представлять собой любое количество подобных списков. Это могут быть перечни имен клиентов, сотрудников, учителей, студентов, оценок, накладных, счетов-фактур, складских запасов, актов продаж, адресов, городов, стран; данные о кредиторской и дебиторской задолженностях, инвестициях и т.д. Данными могут быть любые перечни вещей, состояние которых требуется отслеживать. Это может быть коротенький список в несколько строк, а может быть громаднейший, состоящий из сотен тысяч строк. Электронные таблицы Microsoft Excel в принципе могут содержать более 1 048 000 строк и более 16 000 столбцов. Еще бо́льшими по размеру базами данных, составленными из сотен миллионов строк, можно управлять с помощью инструмента Power Pivot, о котором говорится в главе 8 «Расширение возможностей сводных таблиц с помощью инструмента Power Pivot». Каждая строка рабочего листа Excel — это транзакция или запись. Каждый столбец — это поле.

Какими должны быть данные

Чтобы данные можно было использовать в сводной таблице, они должны быть определенным образом структурированы. При необходимости для такой очистки можно использовать пакет Power Query, который рассматривается далее в этой главе, а также другие, более традиционные методы, встроенные в Excel. На рис. 1.1 показан пример «хороших», а на рис. 1.2 — «плохих» данных. Ниже приведены способы структурирования данных таким образом, чтобы они могли использоваться в сводной таблице.

• Важно, чтобы заголовки столбцов (названия полей) отображались в самой верхней строке, которая также называется строкой заголовков.

• Для заголовков столбцов следует использовать только одну строку (верхнюю).

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

• Строка заголовков не обязательно должна быть строкой номер 1 в электронной таблице Excel, но она должна быть первой строкой списка данных. Аналогично первый столбец не обязательно должен быть столбцом A этой таблицы, но он должен быть первым столбцом этого списка.

• Если над строкой заголовков имеются еще какие-то записи, то непосредственно перед ней должна быть хотя бы одна полностью пустая строка, а если перед первым столбцом списка данных существуют какие-то непустые столбцы, то перед ним должен находиться хотя бы один пустой столбец.

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

• В разных местах списка данных могут быть пустые ячейки, но целая строка или целый столбец этого списка не должны быть пустыми.

• Каждая отдельная операция/транзакция должна занимать только одну строку в электронной таблице Excel.

• В каждом столбце должна быть информация только одного вида. Например, столбец с заголовком «Страна» должен содержать только названия стран.

• Содержимое всех ячеек одного столбца должно иметь одинаковый тип данных: например, только текст, только числа или только даты.

• В списке данных не должно быть объединенных ячеек, которые образуются, когда несколько ячеек сливаются в одну большую. Наличие объединенных ячеек не позволит создать сводную таблицу.

Рис. 1.1. Пример «хорошей» структуры данных

Рис. 1.2. Пример «плохих» данных

ПРИМЕЧАНИЕ

Проверка наличия объединенных ячеек

Ниже приводится быстрый способ проверить, есть ли в вашем списке данных объединенные ячейки:

1. Выделите какую-либо ячейку внутри диапазона данных.

2. Выделите весь диапазон данных, нажав одновременно клавиши Ctrl + A.

3. Перейдите на вкладку Главная.

4. Щелкните по значку Найти, как показано на рис. 1.3.

5. В диалоговом окне Найти и заменить щелкните по кнопке Параметры. Появится список параметров, как показано на рис. 1.4.

6. Далее щелкните по кнопке Формат. Появится диалоговое окно Найти формат, как показано на рис. 1.5.

Рис. 1.3. Выбор значка Найти

Рис. 1.4. Переход в следующее диалоговое окно по кнопке Параметры

Рис. 1.5. Выбор вкладки Выравнивание в диалоговом окне Найти формат

7. Перейдите на вкладку Выравнивание.

8. Поставьте галочку рядом с опцией Объединение ячеек.

9. Щелкните по кнопке OK.

10. Щелкните по кнопке Найти все. После этого будут найдены и выделены все объединенные ячейки в выбранном списке.

11. Закройте диалоговое окно Найти и заменить.

12. Если в списке данных были обнаружены объединенные ячейки, выделите этот список, переместите указатель мыши внутрь выделенной области, нажмите правую кнопку мыши, выберите пункт выпадающего меню Формат ячеек и кликните один или несколько раз по чекбоксу рядом с опцией Объединить ячейки, пока он не станет пустым. При этом все объединенные ячейки будут разделены.

Типы данных, которые можно использовать в сводных таблицах Excel

Данные для сводной таблицы могут поступать из разных источников. Они могут быть как в виде файла Excel, так и в других форматах. Внешние для Excel данные придется тем или иным образом импортировать и разместить их в рабочем листе Excel. Однако если в таблице более 1 048 000 строк, то их придется импортировать с помощью инструмента Power Pivot, о чем мы поговорим в главе 8 «Расширение возможностей сводных таблиц с помощью инструмента Power Pivot». Ниже перечислены наиболее распространенные источники данных, которые возможно импортировать в Excel, а затем внести в сводную таблицу:

• рабочая книга Excel;

• текстовый или CSV-файл;

• набор данных в формате XML;

• набор данных в формате JSON;

• файл PDF (этот исходный формат не является желательным, но его использование в принципе возможно, особенно если ничего другого нет);

• база данных SQL Server;

• база данных Access;

• база данных Oracle;

• база данных IBM DB2;

• база данных MySQL;

• база данных PostgreSQL;

• база данных Sybase;

• база данных Teradata;

• база данных SAP HANA;

• данные из Azure;

• данные из PowerBI;

• данные из Dataverse;

• потоки данных;

• список SharePoint;

• Microsoft Exchange;

• Microsoft Dynamics 365;

• Salesforce;

• веб-сайт, содержащий таблицу с данными;

• канал OData;

• файл Hadoop;

• источник данных ODBC;

• источник данных OLEDB.

Существуют и другие возможные источники данных, но выше перечислены основные, которые встречаются на практике. Кроме того, большинство широко используемых баз данных совместимы с ODBC, OLE или с ними обеими, так что почти каждая популярная база данных может служить источником, из которого данные можно импортировать в Excel и затем использовать для формирования сводной таблицы. Если вашей базы данных нет в вышеприведенном списке и она не совместима с ODBC или OLE, то все равно есть большая вероятность того, что в ней предусмотрена возможность экспорта данных либо в текстовый/CSV-файл, либо даже прямо в таблицу Excel. Поэтому в вашем распоряжении все равно должен быть способ преобразования ваших данных в формат Excel. Используя недавно усовершенствованный инструмент Power Query, вы можете импортировать непосредственно в Excel данные практически из любых источников. Power Query — это мощное встроенное в Excel средство, которое позволяет импортировать и структурировать данные из других источников, а также различными способами приводить информацию к нужному виду. Некоторые из этих способов будут продемонстрированы в данной главе.

Работа с данными формата Excel

Вполне вероятно, что у вас уже есть данные в существующей рабочей книге Excel, которые можно вставить в сводную таблицу. Они могут быть представлены как в виде неформатированной таблицы неочищенных данных — это так называемые исходные (первичные, «сырые») данные, или же могут быть структурированы в виде форматированной таблицы (таблицы Excel). На рис. 1.6 показан пример неформатированной, а на рис. 1.7 — форматированной таблицы Excel.

Рис. 1.6. Неформатированная таблица неочищенных данных

Рис. 1.7. Форматированная таблица

Любая из них будет прекрасным источником для создания сводной таблицы, хотя для этой цели форматированная таблица имеет свои преимущества. Среди них можно выделить следующие.

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

• Форматированные таблицы можно использовать для создания единой сводной из нескольких листов путем объединения по общим полям. Этот прием рассматривается в главе 7 «Создание сводной таблицы из нескольких электронных таблиц» и в главе 8 «Расширение возможностей сводных таблиц с помощью инструмента Power Pivot».

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

• Отформатированная таблица автоматически удалит все объединенные ячейки, которые ранее имелись в списке, и не позволит объединять какие-либо находящиеся внутри себя ячейки.

• Сортировка и фильтрация данных в форматированных таблицах облегчаются благодаря наличию выпадающих списков в верхней части каждого столбца.

• Заголовки в отформатированной таблице будут оставаться в верхней части каждого столбца по мере прокрутки вниз. В неформатированных таблицах этого не происходит, если не использовать встроенную в Excel команду Закрепить области, к которой можно получить доступ на вкладке Вид.

• Отформатированная таблица готова к использованию в Power Pivot.

• Отформатированная таблица дает подсказки при создании формул.

Вы можете отформатировать таблицу, выполнив следующие действия3:

1. Щелкните по любой ячейке в списке ваших данных.

2. Перейдите на вкладку Главная.

3. Щелкните по опции Форматировать как таблицу, имеющейся на ленте инструментов Excel. Откроется галерея стилей таблиц, как показано на рис. 1.8.

Рис. 1.8. Форматирование данных в виде таблицы

4. Выберите один из стилей. Появится диалоговое окно Форматирование таб­лицы.

5. Убедитесь, что установлен флажок Таблица с заголовками.

6. Щелкните по кнопке OK.

Теперь ваш список представляет собой отформатированную таблицу.

Импорт данных в Excel из внешних источников

Иногда возникает ситуация, когда нужно импортировать в Excel данные из каких-либо внешних источников. Инструмент Power Query позволяет производить импорт данных из файлов самых разных типов и баз данных различных форматов. При импорте автоматически создается таблица, куда заносятся данные в новом формате; на основе этой таблицы можно затем легко создать сводную. В следующем разделе мы рассмотрим шаги, необходимые для импорта данных из наиболее часто встречающихся источников. Работа с другими источниками, которые мы здесь не упоминаем, проводится аналогичным образом.

ПРИМЕЧАНИЕ

Для получения доступа к внешнему источнику данных вам может потребоваться помощь ИТ-отдела. При работе с такими источниками соблюдайте установленные правила и следуйте процедурам, принятым в вашей компании.

Импорт данных из текстового/CSV-файла

Текстовые/CSV-файлы — это простые неотформатированые файлы с текстовой информацией. Они являются универсальным источником данных, поскольку практически все программы для работы с базами данных и электронными таблицами позволяют импортировать данные из текстового/CSV-файла и/или экспортировать в него. CSV-файл — это особый тип текстового файла. Сокращение CSV расшифровывается как comma-separated values, то есть каждое поле данных в таком файле отделяется от других запятыми. Хотя CSV-файлы являются наиболее популярным форматом для хранения текстовых данных, в качестве источников последних могут использоваться файлы и других видов.

Желательно, чтобы названия полей (заголовки столбцов) были в первой строке текстового файла, служащего источником данных. Но в принципе это необязательно. Все это можно добавить вручную после успешного импорта данных в Excel. Также желательно, чтобы исходный текстовый файл имел какую-то узнаваемую структуру. Поля данных должны быть разделены каким-либо символом, обычно одним из следующих: пробел, запятая, точка с запятой, двоеточие или знак табуляции. С другой стороны, файл, содержащий текст в свободной форме, например фрагмент из книги или журнальной статьи, скорее всего, не будет подходящим источником данных, которые можно было бы использовать в сводной таблице.

ПРИМЕЧАНИЕ

Напоминаем, что файлы примеров для этой книги можно загрузить с сайта издательства «Питер». QR-код для скачивания находится в разделе «От издательства». В следующем примере используется файл ProductDescriptions.csv (Описание продукции), входящий в состав набора файлов примеров.

Ниже описаны шаги по импорту данных из текстового/CSV-файла.

1. Откройте новую пустую рабочую книгу и перейдите на вкладку Данные.

2. Щелкните по опции Получить данные и выберите пункты Из файла Из текстового/CSV-файла в открывшемся меню, как показано на рис. 1.9. Появится диалоговое окно Импорт данных.

Рис. 1.9. Импорт данных

3. Перейдите в папку, где находится ваш текстовый/CSV-файл, и щелкните по нему левой кнопкой мыши.

4. Щелкните по кнопке Импорт. В новом окне появятся данные из выбранного вами файла, как показано на рис. 1.10.

5. Возможно, вам придется изменить символ разделителя полей, но обычно Excel правильно его определяет. Если все же вам придется его заменить на какой-то другой, попробуйте использовать каждый символ из выпадающего списка разделителей. Таким перебором добейтесь, чтобы данные выглядели пригодными для их разнесения по столбцам. В CSV-файле в качестве разделителя обычно используется запятая.

6. Щелкните по кнопке Загрузить.

Рис. 1.10. Импортируемые данные

После этого данные загрузятся на отдельный лист Excel в виде отформатированной таблицы. Вы увидите название этой таблицы в находящемся справа окне Запросы и подключения, которое можно использовать для очистки данных или изменения вида содержащей их таблицы с помощью Power Query.

Иногда при импорте из текстового файла Excel не распознает первую строку как строку заголовков. Если в первой строке действительно содержатся заголовки полей, вы можете сделать ее строкой заголовков вручную. А еще бывает, что в первой строке импортированного списка данных появляются заголовки типа Столбец 1 и Столбец 2, в то время как реальные названия полей приведены на одну или даже несколько строк ниже. Если вы столкнулись с такой проблемой, вам придется удалить все записи, располагающиеся выше строки, содержащей заголовки. Затем ее можно сделать строкой заголовков таблицы импортированных данных, выполнив перечисленные ниже действия.

ПРИМЕЧАНИЕ

Если у вас возникли какие-либо проблемы с занесением названий столбцов в первую строку, обратитесь к разделу «Удаление столбцов и строк» далее в этой главе.

Итак, чтобы в первой строке данных появились реальные названия полей, сделайте следующее:

1. Щелкните правой кнопкой мыши по названию таблицы в окне Запросы и подключения и выберите пункт меню Изменить. В результате появится окно Редактор Power Query, как показано на рис. 1.11.

Рис. 1.11. Первая строка таблицы как строка заголовка

2. В окне Редактор Power Query перейдите на вкладку Главная.

3. Выберите команду Использовать первую строку в качестве заголовков. Теперь первая строка стала строкой названия полей.

4. Закройте окно Редактора Power Query, сохранив внесенные изменения.

Импорт из базы данных Access

Microsoft Access уже много лет является стандартной базой данных для персональных компьютеров. Она входит в состав пакета Microsoft 365. Многие пользователи используют ее для обработки всевозможной информации. База Access — это прекрасный источник данных для создания сводных таблиц. Файл базы данных Access содержит таблицы и соответствующие запросы/представления, а также другие объекты. И таблицы, и запросы можно импортировать в Excel. Любые файлы баз данных, созданные в Access 2000 или в более поздних версиях, подходят для этой цели. Что касается баз данных, созданных в более ранних версиях Access, то они могут вызывать сложности и ошибки при импорте. Однако всегда можно преобразовать такие файлы в нужный формат при помощи Access более новых версий.

ПРИМЕЧАНИЕ

В наборе файлов с примерами для этой книги имеется база данных Access под названием States.accdb (Штаты). Вы можете использовать ее для иллюстрации описываемых действий.

Чтобы импортировать данные из базы данных Access, нужно выполнить следующие действия:

1. Перейдите на вкладку Данные.

2. Пройдите по пунктам меню Получить данныеИз базы данныхИз базы данных Microsoft Access. Появится диалоговое окно Импорт данных.

3. Перейдите в папку, где находится файл базы данных Access, и щелкните по названию файла, из которого требуется импортировать данные.

4. Нажмите кнопку Импорт. Как показано на рис. 1.12, появится окно Навигатор, в котором будут отображены таблицы и запросы, имеющиеся в этой базе данных.

Рис. 1.12. Окно навигатора при импорте данных из базы данных Access

5. Щелкните по названию таблицы или запроса, который вы хотите импортировать, а затем нажмите Загрузить.

Данные будут импортированы на отдельный лист в виде отформатированной таблицы. В окне Запросы и подключения справа появится название этой таблицы. С помощью Power Query можно проводить очистку содержащихся в ней данных или изменять их вид.

Импорт данных с веб-страницы

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

Возможно, вы уже пытались просто скопировать данные с какого-либо веб-сайта и вставить их в Excel. Я тоже пробовал. Результаты подобных попыток обычно плачевные. Но даже если в итоге вы достигнете того, к чему стремились, то потратите слишком много времени, пытаясь изменить исходные данные так, чтобы их можно было использовать в Excel.

Ниже показан более грамотный способ импортирования данных с веб-страницы.

1. Откройте веб-браузер, например Google Chrome, Edge, Safari или любой другой, и перейдите на веб-сайт, с которого вы хотите получить данные. В нашем конкретном примере я использую сайт https://finance.yahoo.com/gainers.

2. Выделите URL веб-сайта и скопируйте его.

3. Откройте существующую или создайте новую книгу Excel.

4. Перейдите на вкладку Данные.

5. В левой части ленты выберите пункты Получить данныеИз других источ­ни­ковИз Интернета. Появится диалоговое окно для импорта данных, как показано на рис. 1.13.

Рис. 1.13. Окно импортирования данных с веб-страницы

6. Вставьте скопированный на шаге 2 URL-адрес веб-сайта или введите его вручную (в этом случае убедитесь, что вы не сделали никаких ошибок — поэтому лучше копировать и вставлять) и нажмите OK. Excel попытается подключиться к веб-сайту и получить доступ к таблицам с данными, если таковые имеются на этом сайте.

7. Если появится окно Доступ к веб-содержимому, просто нажмите Подключение. Вы увидите окно Навигатор, в котором будут показаны найденные на этом сайте таблицы, если они там есть.

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

Данные будут импортированы и помещены на отдельный лист в виде отформатированной таблицы. В окне Запросы и подключения в правой части экрана вы увидите эту таблицу, а содержащиеся в ней данные можно будет очищать или структурировать с помощью Power Query.

Подключение к базе данных с помощью ODBC

Большинство популярных баз данных совместимы с ODBC. Аббревиатура расшифровывается как Open Data Base Connectivity (открыть подключение к базе данных). Это протокол, который используется для того, чтобы различные системы управления базами данных могли обмениваться данными друг с другом. Примерами ODBC-совместимых баз данных являются SQL Server, Oracle, MYSQL, dBase, Paradox, IBM DB2 и Sybase.

По моему опыту, в большинстве случаев ИТ-отделы компаний не предоставляют прямой доступ к корпоративным базам данных, поскольку должны защищать конфиденциальную информацию. ODBC-соединение позволяет открывать доступ только к запрашиваемой части данных, сохраняя конфиденциальность всего остального. Вам придется обратиться к своим ИТ-специалистам с просьбой установить и настроить драйвер ODBC на вашем рабочем компьютере. Сообщите ИТ-службе своей организации, какие данные вам нужны, и обязательно используйте формат «Только для чтения» (Read only), когда будете делать запрос. В вашей компании могут быть приняты специальные процедуры, которым необходимо следовать при работе с корпоративной базой данных, и я настоятельно рекомендую вам их придерживаться.

Получив от ИТ-отдела необходимые права доступа, вы можете импортировать данные из базы при помощи ODBC, выполнив следующие действия:

1. На вкладке Данные выберите Получить данныеИз других источниковИз ODBC. В результате появится диалоговое окно Из ODBC, как показано на рис. 1.14.

Рис. 1.14. Импорт данных из