База данных в excel: как создать и использовать / skillbox media

Преимущества модели данных

Метод And before отлично подходит, когда вы работаете с очень небольшим количеством данных. Тем не менее, у использования функции модели данных в Excel есть преимущества. Вот некоторые преимущества:

Проверка и обновление формул может быть произвольной при работе с большим количеством таблиц. В конце концов, вам нужно убедиться, что все формулы заполнены до нужной ячейки. И после добавления новых столбцов формулы LOOKUP также необходимо расширить. Модель данных требует совсем немного времени при настройке , чтобы связать таблицу. При настройке используется общий столбец. Однако столбцы, которые вы добавляете позже, автоматически добавляются в модель данных.
Работа с большими объемами данных часто приводит к очень медленной работе листа из-за вычислений.. Однако модель данных корректно обрабатывает большие объемы данных , не замедляя работу вашей компьютерной системы.
Excel 2016 имеет ограничение в 1,048,576 строк. Однако количество строк, которые вы можете добавить в память модели данных, практически не ограничено . В 64-битной среде нет жестких ограничений на размер файла. Размер книги ограничен только доступной памятью и системными ресурсами.
Если ваши данные находятся только в вашей модели данных, вы значительно сэкономите на размере файла .

«Текстовые фильтры»«Настраиваемый фильтр…»

Особенности формата ячеек

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

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

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

Реестр документов и печатная форма в Excel

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

Ведение реестра документов

Реестр документов удобно заполнять в «Умной таблице» Excel, если не предполагается использование объединенных ячеек.

Далее мы рассмотрим реестр с данными сотрудников, который может использоваться для хранения этих данных и распечатки различных документов. На практике это был реестр Трудовых договоров, из которого распечатывались: Трудовой договор, Приложение к трудовому договору, Договор о материальной ответственности, Приказ о приеме на работу, Изменения к трудовому договору, Соглашение о расторжении трудового договора.

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

Пример реестра документов в «Умной таблице» Excel

Для заполнения реестра данными можно использовать автоформу, подробнее о которой можно прочитать в разделе Работа с «Умной таблицей».

Создание печатной формы

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

В нашем примере используется печатная форма Договора о материальной ответственности, текст которого скопирован на рабочий лист Excel из документа Word:

Договор о материальной ответственности

Если печатная форма на рабочем листе Excel готова, остается переместить (или скопировать) ее в книгу с реестром. Если реестр еще не создан, можно просто добавить новый лист в книгу с печатной формой и назвать его «Реестр…».

Заполнение печатной формы

Для заполнения печатной формы данными из реестра будем использовать встроенную функцию Excel — «ВПР». Ориентиром поиска по таблице будет номер записи в реестре по порядку, поэтому ячейку с номером договора укажем в формулах «ВПР» печатной формы как «Искомое_значение».

Аргументы функции «ВПР»

В мастере функций набраны аргументы для формулы в ячейке «L4», которая после ввода примет следующий вид: =ВПР(‘Печать ДМО’!G1;’Реестр ДМО’!A2:J102;2;ЛОЖЬ)

Во все остальные ячейки с переменными значениями копируется эта формула, и в ней заменяется только «Номер_столбца».

Теперь при записи в ячейку «G1» номера нужной строки реестра, печатная форма Договора о материальной ответственности заполнится данными соответствующего работника.

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

Определение отношений между таблицами

Первое правило сводных таблиц: отношения между таблицами определяются в системе отчетов сводной таблицы, с использованием инструмента Отношения. Не пытайтесь с самого начала определить связи между таблицами – инструмент формирования отчетов сводных таблиц все равно их не воспримет.

Выделяем таблицу с продажами, во вкладке Вставка переходим в группу Таблицы, щелкаем Сводная таблица.

В появившемся диалоговом окне Создание сводной таблицы в поле Таблицы или диапазон убеждаемся, что указана таблица Продажи. Также ставим галку напротив поля Добавить эти данные в модель данных и щелкаем кнопку ОК.

Excel создаст новый лист с пустой сводной таблицей. В левой части экрана появится панель Поля сводной таблицы. Чтобы свести данные обоих таблиц, в панели Поля сводной таблицы вкладки Активная проставьте галочки напротив пунктов Квартал 1, Квартал 2, Квартал 3 и Квартал 4

Excel построит сводную таблицу с данными по кварталам, пока не обращайте на нее внимание. Далее в этой же панели переходим на вкладку Все, где вы увидите обе наши таблицы

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

В появившемся диалоговом окне Создание связи необходимо определить отношения между таблицами. Выберите из выпадающего списка Таблица пункт Продажи, а из выпадающего списка Столбец (Чужой) пункт ID Продаж. Помните, что ID Продаж единственное поле, которое находится в обоих таблицах. Из списка Связанная таблица выбираем Основной, из списка Связанный столбец (первичный ключ) — пункт ID Продаж. Жмем ОК.

Excel создаст связи и отобразит результирующий отчет на экране. Дайте имя вашему отчету, и он будет готов.

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

Как ввести отчет в отдел продаж

Менеджеры – люди активные. В отделе продаж развивается конкурентность меж сотрудниками больше, чем где-либо. Этому содействует стиль работы: кто-то продал сейчас больше, кто-то меньше. Сотрудники раз в день делятся своими достижениями. Но без определенного отчета управляющий может составить лишь общее представление о продажах. Случается, что представление далековато от реальности. Чтоб этого не происходило, следует ввести в компании каждодневную отчетность.

Даже самый обычной отчет дает минимум три выгоды:

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

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

количество завлеченных клиентов;
закрытые сделки;
настоящий доход.

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

База клиентов в Excel (простой вариант)

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

Скачать файл для ведения базы клиентов (.xls)

Лист «Мои услуги» – представляет список, в который можно включить до 10 услуг. Услуги из этого списка Вы сможете выбрать при добавлении информации о клиенте в базу данных.

Лист «Клиенты» – база клиентов, с которыми Вы работаете или работали. База включает следующую информацию:

Порядковый номер клиента. Позволяет понять, насколько велико число Ваших клиентов.
Имя клиента – можно вводить имя или ФИО, а также название компании
Телефон
E-mail
Что заказывает – поле заполняется путем выбора услуги из выпадающего списка. Если клиент заказывает несколько услуг, можно выбрать из списка основную, а другие указать в комментариях.
Комментарий – описание клиента в свободной форме, особенности работы с заказчиком.
Дата первого заказа – дата получения первого заказа. Позволяет понять, насколько долго Вы уже работаете с клиентом.
Дата последнего заказа – важный параметр, позволяет отследить последнюю продажу клиенту. Например, Вы можете отсортировать клиентов по дате последнего заказа и посмотреть, кто из клиентов давно ничего не заказывал – написать им, напомнить о себе и, возможно, получить новый заказ.

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

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

Как работать с простой базой клиентов в Excel?

Добавляйте в базу всех новых клиентов, которые оформили реальный заказ (т.е. тех, кто просто позвонил или один раз что-то написал, но не купил – добавлять не нужно);
Раз в полгода отслеживайте клиентов, которые давно не делали заказы. Напишите им, напомните о себе. Чаще, чем раз в полгода, писать не стоит – иначе Вы рискуете слишком надоесть клиенту. Но это верно только для фрилансеров, в каких-то сферах стоит чаще напоминать о себе
Если Вы чувствуете спад в количестве заказов, сделайте клиентам специальное предложение. Например, сделайте скидку на копирайтинг и напишите постоянным клиентам, кто заказывает тексты, о снижении цен.
Используйте столбец с комментариями, чтобы указать особенности каждого клиента, которые помогут Вам эффективно работать с заказчиком. Например, каким-то заказчикам нужно помочь с составлением технического задания – отметьте это в комментариях, чтобы не забыть помочь с ТЗ.

Как вести базу клиентов в Excel

Чтобы упростить поиск данных в базе, упорядочим их. Для этой цели подойдет инструмент «Сортировка».

Выделяем тот диапазон, который нужно отсортировать. Для целей нашей выдуманной компании – столбец «Дата заключения договора». Вызываем инструмент «Сортировка».
При нажатии система предлагает автоматически расширить выделенный диапазон. Соглашаемся. Если мы отсортируем данные только одного столбца, остальные оставим на месте, то информация станет неправильной. Открывается меню, где мы должны выбрать параметры и значения сортировки.

Данные в таблице распределились по сроку заключения договора.

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

БД в процессе деятельности фирмы разрастается до невероятных размеров. Найти нужную информацию становится все сложнее. Чтобы отыскать конкретный текст или цифры, можно воспользоваться одним из следующих способов:

Одновременным нажатием кнопок Ctrl + F или Shift + F5. Появится окно поиска «Найти и заменить».
Функцией «Найти и выделить» («биноклем») в главном меню.

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

В программе Excel чаще всего применяются 2 фильтра:

Автофильтр;
фильтр по выделенному диапазону.

Автофильтр предлагает пользователю выбрать параметр фильтрации из готового списка.

На вкладке «Данные» нажимаем кнопку «Фильтр».
После нажатия в шапке таблицы появляются стрелки вниз. Они сигнализируют о включении «Автофильтра».
Чтобы выбрать значение фильтра, щелкаем по стрелке нужного столбца. В раскрывающемся списке появляется все содержимое поля. Если хотим спрятать какие-то элементы, сбрасываем птички напротив их.
Жмем «ОК». В примере мы скроем клиентов, с которыми заключали договоры в прошлом и текущем году.
Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры».
Если мы хотим видеть в таблице клиентов, с которыми заключили договор на 3 и более лет, вводим соответствующие значения в меню пользовательского автофильтра.

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

Выделяем те данные, информация о которых должна остаться в базе видной. В нашем случае находим в столбце страна – «РБ». Щелкаем по ячейке правой кнопкой мыши.
Выполняем последовательно команду: «фильтр – фильтр по значению выделенной ячейки». Готово.

Если в БД содержится финансовая информация, можно найти сумму по разным параметрам:

сумма (суммировать данные);
счет (подсчитать число ячеек с числовыми данными);
среднее значение (подсчитать среднее арифметическое);
максимальные и минимальные значения в выделенном диапазоне;
произведение (результат умножения данных);
стандартное отклонение и дисперсия по выборке.

Порядок работы с финансовой информацией в БД:

Выделить диапазон БД. Переходим на вкладку «Данные» — «Промежуточные итоги».
В открывшемся диалоге выбираем параметры вычислений.

Инструменты на вкладке «Данные» позволяют сегментировать БД. Сгруппировать информацию с точки зрения актуальности для целей фирмы. Выделение групп покупателей услуг и товаров поможет маркетинговому продвижению продукта.

Готовые образцы шаблонов для ведения клиентской базы по сегментам.

Шаблоны можно подстраивать «под себя», сокращать, расширять и редактировать.

Фиксация «шапки» базы данных

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

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

Быстрый анализ в Excel

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

Как работать

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

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

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

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

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

Пакет анализа от Майкрософт – очень функциональная надстройка, которая делает пользователя настоящим профессионалом. Она позволяет выполнить почти любую обработку количественных данных, но она довольно сложная для начинающего пользователя. На официальном сайте справки Майкрософт есть детальная инструкция по тому, как использовать разные виды анализа с помощью этого пакета.

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

Известно, что в Excel можно создавать таблицы и работать с ними. Однако, часто возникает необходимость загрузить готовую таблицу из другого источника данных. Давайте рассмотрим, как можно в Excel загрузить данные из файла Access.

Предположим, мы имеем такую базу данных Access:

Чтобы загрузить данные, откроем пустой файл Excel, выберем в меню Данные — Получить внешние данные из Access.

В появившемся окне, выберем необходимый файл Access. Далее, появится следующее окно:

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

Теперь мы получили таблицу в Excel, которая связана с данными из файла Access . Но наша таблица не является простой, фактически она является запросом к базе данных. Это так называемая Умная таблица, которую можно обновить и получить «свежие» данные (щелкаем правой кнопкой мыши на таблицу и выбираем «Обновить»).

Способ 2. Импорт данных

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

Теперь откроем книгу, где мы хотим создать выпадающий список

После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.

После нажатия на ОК появится еще одно последнее окно

Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.

После нажатия на кнопку ОК, Excel загрузит данные из созданной таблицы из файла Справочник в наш текущий файл Бланк и отформатирует их в виде таблицы.

Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.

В строке формул можно увидеть имя импортированного списка

Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:

Выделяем ячейки, где хотим создать выпадающие списки.
На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник») . В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник») .

Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.

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

Внешний вид базы данных в Excel

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

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

Ну и раз уж это база данных для ведения кассы, в любую ячейку закрепленной строки, правее наименований полей, вставьте формулу: =СУММ(C:C)-СУММ(E:E), чтобы всегда видеть текущий остаток денег в кассе.

Как узнать больше о работе в Excel

В Skillbox Media собрали в одном месте 15 статей и видео об инструментах Excel, которые ускорят и упростят работу с электронными таблицами.
Также в Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1»). У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

2.6.1. Добавление промежуточных итогов.

Добавление промежуточных и окончательных итогов выполняется после сортировки исходной базы по выбранному полю. Выполним сортировку исходной базы по полю «Бригада» и добавим промежуточные и общий итоги. Для добавления итогов откроем окно диалога «Промежуточные итоги». Для этого выполняем команды меню Данные Итоги. В окне диалога зададим поле, в котором будет отслеживаться изменение значений (например поле «Бригада»). Затем укажем в строке «Операция» тот вид операции, который нужен для выполнения задания(Сумма, Среднее и т.д.). В списке окна диалога «Добавить итоги по» укажем, каких еще полях базы данных необходимо выполнить аналогичные действия. Окно диалога с установленными параметрами показано на Рис.2.15.

Результат выполнения операции по добавлению итогов отобразим на Рис.2.16. Аналогичные действия выполним для добавления итогов по полю «Специальность». Отсортируем исходную базу по полю «Специальность» по возрастанию и при каждом изменении в этом поле добавим промежуточные итоги. Определим суммарные значения начисленной суммы и подоходного налога по специальностям. Для этого в окне диалога «Промежуточные итоги» выберем поле, изменения в котором приведут к добавлению итогов в таблицу. Это поле «Специальность», и установим операцию для выполнения («Сумма»). Установим в окне диалога, для каких полей будет выполняться эта операция (Начислено, По/нал). Результаты отобразим на Рис.2.17.