ЛЕКЦИЯ 9. РАБОТА С БАЗАМИ ДАННЫХ В MS EXCEL.СОЗДАНИЕ МАКРОСОВ Основные понятия и термины MS Excel может работать как с простыми и небольшими по размерам, так и с более сложными, занимающими большой объем дискового пространства списками данных. Эти списки часто называются базами данных. Однако в MS Excel база данных – это список, состоящий из одного или более столбцов.
Со списками можно производить такие операции как поиск необходимой информации, анализ, извлечение, сортировка. Список состоит из записей. Запись делится на поля. Одни и те же поля из различных записей предназначаются для данных одного типа. Каждая запись располагается в отдельной строке, а для каждого поля отводится определенный столбец.
Строка, содержащая имена полей, называется строкой заголовков. Использование формы данных для ввода новых данных Сразу после ввода имен полей и первой строки списка можно использовать наиболее простой способ ввода информации в список, а именно – автоматически создаваемую форму данных. Для этого: 1. Выделить ячейку внутри списка. 2. Выбрать команду ДанныеФормаДобавить.
Работа с базой данных в табличном процессоре Microsoft Excel
Рекомендуемые материалы
Лекции за 1 курс
Информатика
Г. Шилтд — Самоучитель C++ (PDF)
Практика расчётов на ПЭВМ
Лаба Кивва Кирилл Андреевич ИУ7 (для РЛ) 2020
Информатика
Методические пособия по C++ Г.С.Иванова 3 части
Объектно-ориентированное программирование (ООП)
Лекции электронная версия
Информатика
ГОТОВАЯ РАБОТА НА ЗАЧЕТ. анимация + расчет
Информатика
3. Ввести данные в соответствующие поля. Для быстрого перемещения от поля к полю используется клавиша TAB. Нажатие клавиши ENTER эквивалентно нажатию кнопки Добавить. Поэтому после ввода последнего поля следует нажать ENTER. 4. Чтобы вернуться к рабочему листу следует нажать кнопку Закрыть.
5. Чтобы вычисляемые поля не занимали место в форме данных, перед выбором команды ДанныеФорма следует скрыть столбцы с формулами. В форме данных для просмотра записей используется полоса прокрутки. Использование формы данных для поиска данных и редактирования Чтобы с помощью формы данных найти необходимые записи, нужно: 1. Выделить какую-либо ячейку списка. 2.
Выполнить команду ДанныеФормакнопка Критерии. 3. Перейти к полю, которое будет участвовать в определении критерия и ввести критерий поиска. Перейти к следующему полю, если для поиска необходим не один критерий. 4. Нажать кнопку Далее, чтобы перейти к записи, которая удовлетворяет введенному критерию поиска.
Для редактирования данных в базе данных следует открыть форму данных, задать критерий и перейти к первой, удовлетворяющей критерию, записи. Отредактировать поля этой записи. При необходимости отменить сделанные изменения до перехода к другой записи нужно нажать кнопку Вернуть. Можно ввести новый критерий и отредактировать другие записи.
Можно удалить найденную запись (кнопка Удалить). Удаленные записи не могут быть восстановлены. Чтобы сохранить последние изменения в базе данных и вернуться к рабочему листу следует нажать кнопку Закрыть. Сортировка данных Сортировка позволяет выстраивать данные в алфавитном или в цифровом порядке по возрастанию или убыванию.
Что такое базы данных в Excel [Работа с базами данных в Экселе]
При выборе команды ДанныеСортировка появляется диалоговое окно. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля, используемые для упорядочения списка. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля, используемые для упорядочения списка.
Порядок действий при сортировке: 1. Сохранить рабочую книгу под другим именем, чтобы можно было вернуться к первоначальному порядку данных, если данные в результате сортировки окажутся перемешанными. 2. Выделить диапазон ячеек, который необходимо сортировать. 3. Выполнить ДанныеСортировка. 4.
В раскрывающемся списке Сортировать по выбрать первое поле, по которому следует упорядочивать данные. Это поле также называется первым ключом сортировки. 5. Выбрать переключатель по возрастанию или по убыванию. 6.
Второе поле сортировки Затем по используется если возникает повторение значения первого поля. А третье поле сортировки В последнюю очередь по используется, если повторяется значение и первого и второго поля. При использовании этих полей также устанавливается переключатель по возрастанию или по убыванию. 7. Нажать ОК.
Создание промежуточных итогов 1. Выделить ячейку базы данных, выполнить команду ДанныеИтоги. Появится диалоговое окно Промежуточные итоги. 2. Указать как группировать данные, выбрав значение в списке При каждом изменении в 3. Выбрать операцию, выполняемую над данными.
4. Выбрать данные, которые должны участвовать в расчетах, установив в списке Добавить итоги по флажки требуемых столбцов данных. Чтобы подвести итоги по нескольким столбцам одновременно, установите флажки для каждого из них. 5. Чтобы заменить все старые промежуточные итоги следует установить флажок Заменить текущие итоги. 6.
Чтобы выводились только общие итоги следует в диалоговом окне Промежуточные итоги отключить опцию Итоги под данными. Чтобы удалить промежуточные итоги следует выполнить команду ДанныеИтогиУбрать все. С помощью структуры списка можно вывести : 1. Только общие итоги (кнопка 1 слева от строки с наименованиями столбцов) 2. Общие и промежуточные итоги (кнопка 2).
3. Полный список (кнопка 3). Поиск данных с помощью Автофильтра Перед применением Автофильтра следует убедиться, что он не используется для другого списка. Затем: Выделить ячейку внутри списка. Выполнить команду ДанныеФильтрАвтофильтр. Раскрыть список, соответствующий полю, которое следует включить в критерий.
Выбрать желаемый критерий, а именно: Все, Первые 10, Условие. Например, чтобы показать данные только по автомобилю Порше 911 (лаб.раб.8, п.9.) следует: – раскрыть список, соответствующий полю Товар (кнопка со стрелкой справа от названия поля); – выбрать строку Условие; – в диалоговом окне Пользовательский Автофильтр указать равно Порше 911.
Чтобы отобразить полный список следует выполнить команду ДанныеФильтрОтобразить все. Чтобы снять Автофильтр со списка следует выполнить ДанныеФильтрАвтофильтр(снять галочку) Расширенный фильтр Для использования расширенного фильтра необходимо создать диапазон критериев, где будут задаваться условия поиска данных.
Верхняя строка такого диапазона должна содержать заголовки полей, которые в точности повторяют заголовки полей в базе данных. Проще всего такое совпадение можно обеспечить путем копирования заголовка таблицы базы данных в свободную область. Кроме того, диапазон критериев должен включать хотя бы одну пустую строку ячеек сразу под заголовком.
В этой строке или строках записываются условия поиска, при чем все, что записано в столбце под заголовком поля, относится именно к этому полю. Критерий поиска может быть составлен с помощью логических функций И() и ИЛИ() по следующим правилам: 1. Если несколько условий введено в одной строке, показаны будут только те записи, которые удовлетворяют всем условиям одновременно (функция И()) (рис. 10.2).
| КОД ЗАКАЗА | ТОВАР | ЦЕНА | КОЛИЧЕСТВО | СУММА |
| >10 |
Рис. 10.2. Пример записи условия с использованием логической функции И() 2. Если по одному условию введено в разных строках, то показаны будут те записи, которые удовлетворяют хотя бы одному условию (функция ИЛИ()) (рис. 10.3).
| КОД ЗАКАЗА | ТОВАР | ЦЕНА | КОЛИЧЕСТВО | СУММА |
| Пежо | ||||
| >10 |
Рис. 10.3. Пример записи условия с использованием логической функции ИЛИ() Порядок поиска данных с помощью Расширенного фильтра: 1. В диапазоне критериев ввести условия поиска. 2. Выделить ячейку внутри списка (базы данных) и выполнить команду ДанныеФильтрРасширенный фильтр. Появится диалоговое окно Расширенный фильтр.
3. Установить параметр в группе Обработка. При выборе переключателя Фильтровать список на месте не удовлетворяющие критерию записи будут скрыты. При выборе переключателя Скопировать результат в другое место список останется нетронутым, а соответствующие критерию поиска записи будут помещены в указанный пользователем диапазон.
4. В поле Исходный диапазон должен быть указан диапазон базы данных, включающий и заголовок и все данные базы. 5. В поле Диапазон критериев следует установить курсор, а затем выделить диапазон критериев, включая и заголовок диапазона, и строки с условиями поиска.
6. Если выбран переключатель Скопировать результат в другое место, то в поле Поместить результат в диапазон следует ввести адрес ячейки, которая будет являться первой в том диапазоне, в котором выдается результат. 7. Нажать ОК.
Создание макросов Макрорекодер – это средство EXEL, которое записывает все производимые над рабочим листом действия и интерпретирует их как последовательность команд Visual Basic. Эти команды формируют процедуру (макрос), которую можно выполнить, чтобы повторно произвести записанные в ней действия. Порядок действий: 1. Выполнить команду ВидПанели инструментовФорма.
2. Выполнить щелчок по кнопке с именем Кнопка. Курсор примет вид крестика. Нарисовать кнопку на рабочем листе. Кнопку можно также нарисовать, используя панель инструментов Рисование. 3. Закрыть диалоговое окно Назначить макрос объекту.
4. Выполнить команду СервисМакросНачать запись. Откроется диалоговое окно Запись макроса. В нем следует указать имя макроса, например, Переход и нажать кнопку ОК. 5. Выполнить в четкой последовательности все те действия, которые должны быть записаны в макрос.
Следует помнить, что все лишние действия пользователя, допущенные ошибки и повторения будут также сохранены в процедуре (в макросе). 6. Выполнить команду СервисМакросОстановить запись. 7. По нарисованной кнопке выполнить щелчок правой кнопкой мыши и выполнить Назначить макрос.
8. В диалоговом окне Назначить макрос объекту выбрать макрос с назначенным в пункте 4 именем (Переход). 9. По нарисованной кнопке выполнить щелчок правой кнопкой мыши и выполнить команду Изменить текст. Присвоить кнопке текст, отражающий действие данной кнопки (например, Переход на лист Баланс или Баланс). 10. Выполнить по кнопке щелчок указателем мыши (в виде пальчика) и проверить правильность работы записанного макроса. Приложение А
| Основные понятия | |
| Рабочее поле | Состоит из строк и столбцов. Максимальное количество строк — 16384, столбцов — 256. Каждое пересечение строки и столбца образует ячейку. |
| Номер строки | Определяет ряд в электронной таблице, обозначен на левой границе рабочего поля |
| Буква столбца | Определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются от A-Z, затем AA-AZ, затем BA-BZ и т.д. |
| Ячейка | Первичный элемент таблицы, содержащей данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. |
| Указатель ячейки | Светящийся прямоугольник, определяющий текущую ячейку. |
| Активная (текущая) ячейка | Помечена указателем. По умолчанию ввод данных и некоторые другие действия относятся к текущей ячейке. |
| Блок | Представляет собой прямоугольную область смежных ячеек. |
| Адрес блока | Состоит из координат противоположных углов блока, разделенных двоеточием. |
| Абсолютная адресация | При копировании формул позволяет сохранить ссылку на конкретную ячейку ($A$2,$R$4 и т.д.) |
| Относительная адресация | При ее использовании в формулах MS Excel запоминает расположение относительно текущей ячейки (A12, N7 и т.д.) |
| Смешанная адресация | При копировании формул один параметр адреса изменяется, а другой — нет ($G5, A$18 и т. д.) |
Элементы интерфейса и окна приложения Microsoft Excel
| Элемент | Описание |
| Кнопка управления окном приложения | Значок открытого в окне приложения; раскрывает системное меню приложения |
| Строка меню | Список команд меню, расположенный под заголовком приложения |
| Меню | Раскрывающийся список команд |
| Вешка горизонтальной и вертикальной разбивки окна | Двойная линия в конце полосы прокрутки, которую можно перетащить на другую позицию полосы, чтобы разделить окно на две области для независимого просмотра одного и того же документа |
| Строка состояния | Строка в нижней части окна приложения, в которой поясняются выделенные команды или даются указания и инструкции |
| Ярлычки рабочих листов | Ярлычки с именами рабочих листов рядом с полосой горизонтальной прокрутки; позволяют выбрать определенный лист в рабочей книге |
| Кнопки прокрутки ярлычков | Кнопки слева от ярлычков рабочих листов, помогают быстро перебирать листы рабочей книги |
| Вешка разбивки ярлычков | Двойная линия между ярлычками и полосой горизонтальной прокрутки. Перетаскивается влево или вправо для настройки границы между областью отображения ярлычков и областью отображения полосы горизонтальной прокрутки |
| Панель инструментов | Панель, содержащая кнопки, обеспечивающие быстрый доступ к таким командам и процедурам, как выбор шрифта, построение диаграммы, рисование и т.д. |
| Строка формул | Строка, в которую вводится текст, числа или формулы; расположена ниже строки меню или панели инструментов |
| Строка состояния | Строка внизу окна приложения, поясняющая действия, которые MS Excel готов выполнить по указанной команде |
Способы ссылки на группы ячеек
| Обозначение | Группа ячеек |
| F3 | Ячейка на пересечении столбца F и строки-3 |
| Е10:Е20 | Ячейки с 10-й по 20-ю в столбце Е |
| В15:Е15 | Ячейки с В по Е в строке 15 |
| 5:5 | Все ячейки строки 5 |
| 5:10 | Все ячейки строк с 5-й по 10-ю |
| В:В | Все ячейки столбца В |
| B:J | Все ячейки столбцов c B no J |
| А10:Е20 | Прямоугольная область пересечения строк с 10-й по 20-ю и столбцов с А по Е |
Сообщения об ошибках ввода
| Сообщение | Описание ошибки |
| ##### | Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислении в ячейке, отформатированной как данные типа даты или времени |
| #ЗНАЧ (#VALUE!) | Неверный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число |
| #ДЕЛ/0(#01У/0!) | Деление на 0 |
| #ИМЯ (#NAME!) | MS Excel не может распознать текст, введенный в формулу, например неверное имя функции |
| #Н/Д (#N/A) | Данные ячейки одного из аргументов формулы в данный момент недоступны |
| *#ССЫЛКА (#REF!) | Неверная ссылка на ячейку |
| #ЧИСЛО(#МиМ!) | Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейке |
| #ПУСТО (#NULL!) | Результат поиска пересечения двух непересекающихся областей, то есть неверная ссылка |
В MS Excel используется четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок. Арифметические операторы
| Оператор | Назначение | Пример | Результат |
| + | Сложение | 5+5 | 10 |
| — | Вычитание | 6-1 | 5 |
| * | Умножение | 4*3 | 12 |
| / | Деление | 7/2 | 3,5 |
| % | Процент | 50% | 0,5 |
| ^ | Возведение в степень | 5^2 | 25 |
Операторы сравнения (условные операторы)
| Оператор | Назначение | Пример | Результат |
| = | Равно | 5=8 | ЛОЖЬ |
| > | Больше | 8>5 | ИСТИНА |
| Меньше | 5 | ИСТИНА | |
| >= | Больше или равно | 6>=3 | ИСТИНА |
| Меньше или равно | 7 | ЛОЖЬ | |
| <> | Не равно | 1<>1 | ЛОЖЬ |
Текстовый оператор объединения
| Оператор | Назначение | Пример | Результат |
| “секретарь” | Генеральный секретарь |
Источник: studizba.com
Автоматизация отчетности при помощи SQL и Power BI
В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.
Почему именно эти технологии?
За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.
Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.
Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.
Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:
- Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
- Еще пример, если вы пытаетесь загрузить в модель данные из Google Analytics при помощи встроенного коннектора, то столкнетесь как минимум с двумя проблемами — ограничениями API GA и долгой выгрузкой данных.
Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.
Переходим к делу
Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.
К данному отчету заказчиком предъявляются следующие требования:
- Отчет должен содержать исторические данные по вчерашний день;
- Отчет должен обновляться ежедневно в автоматизированном режиме;
- Помимо Power BI, должна быть возможность подключения к отчету через Excel.
Также отчет должен содержать следующие параметры и показатели:
Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).
В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:
- sessions — данные из Google Analytics загруженные посредством коннектора к Reporting API v4;
- costs — данные по расходам, предварительно загруженные в Google Analytics;
- orders — данные по заказам и доходу из внутренней CRM-системы.
Для работы нам потребуется установить:
- SQL Server Management Studio — для подключения к DWH;
- Power BI Desktop — для создания отчета.
Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.
Готовим данные
Итак, задача понятна, инструменты готовы — за дело!
Создаем таблицу
Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:
- date ;
- sourceMedium ;
- campaign .
Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу utm-метками. О том как правильно ставить метки, читайте в одном из уроков бесплатного онлайн-курса «Digital-аналитика для новичков».
Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:
Запустим его и порадуемся получившемуся результату:
Создаем таблицу
Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.
Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:
Таблица будет иметь следующую структуру (подробнее о типах данных):
При сохранении таблицы укажем название:
И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :
SELECT * FROM paid_traffic_report
Создаем хранимую процедуру
Отлично! Настало время автоматизации
А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).
Засучим рукава и обернем наш скрипт в код процедуры:
Теперь протестируем и вручную вызовем процедуру:
Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:
Осталось настроить ежедневное обновление.
Настраиваем расписание
Настроим вызов нашей процедуры каждое утро по расписанию, благо в Management Studio для этого предусмотрена специальная служба под названием «Агент SQL Server».
Зайдем в агент и добавим новое задание:
Укажем название и придумаем описание:
Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):
Настраиваем время запуска, периодичность и сохраняем:
Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.
Визуализируем данные
Данные готовы, обновление настроено, самое время приступить к визуализации.
Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.
Создаем отчет
Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:
Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:
И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.
Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.
После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:
Настраиваем расписание
Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.
Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):
Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:
Готово. Теперь можем пользоваться отчетом внутри веб-интерфейса, опубликовать отчет в интернете, либо отправить коллегам ссылку на него.
А как же Excel?
Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.
Для этого зайдем в Excel на вкладку «Данные» и создадим новое подключение к серверу баз данных:
После чего останется только указать SQL-запрос и сохранить:
С этого момента данные из нашей таблицы на сервере станут доступны в Excel.
Итог
В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.
Буду рад ответить на ваши вопросы.
Источник: thisisdata.ru