Как делать базу данных в excel

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

Функции баз данных в Эксель

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

1. Поле. Эта составляющая определяет столбик, который используется экселевской функцией. Поле может задаваться текстовым содержимым, помещенным в кавычки.
2. Аргумент (или критерий). Это табличный промежуток, содержащий то или иное условие.
3. База данных. Это непосредственный табличный интервал, который лежит в основе любой БД. Другими словами, — это данные, оформленные в виде перечня.

Каждая из указанных составляющих отсылается на тот или иной табличный интервал.

Создание базы данных в электронных таблицах

К распространенным функциям БД в Эксел относятся «ДСРЗНАЧ», «БДПРОИЗВЕД», «ДМИН» и «ДМАКС», «БИЗВЛЕЧЬ» и т. д.

Как обрабатывать информацию в БД

Работа с базами данных в Экселе может осуществляться значительно проще, если пользователь воспользуется специальными формами. Они пригодятся для обработки информации, ее корректировки. Когда пользователь активирует команду «Форма», Эксель автоматически формирует окошечко с формами.

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

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

Как в Экселе сделать базу данных

Формирование БД особенно важно при работе с закупками, с клиентами, с товарным ассортиментом. Поэтому стоит разобраться, как происходит создание базы данных в Эксель пошагово. Рассмотрим этот процесс на примере создания БД постоянной клиентуры. Допустим, у определенной фирмы появилось много покупателей, закупающих продукт на постоянных условиях. И перед фирмой встает задача регулярного мониторинга сроков, оперативного поиска контактных данных и т. п. Создание БД в этом случае происходит так:

1. Создаются заголовки столбиков.
2. Вводятся непосредственные сведения о клиентах. При этом важно соблюдать единый формат каждого столбика. Например, в одном столбце только цифры и т. д.
3. Переходим на вкладыш «Данные». После этого пользователь сможет воспользоваться БД.
4. Создаваемой базе присваивается название и определяется диапазон ее действия. В диапазон должны войти все табличный ячейки с информацией о клиентах.

Формат табличных ячеек в БД

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

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

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

Эксель работа с базой данных: тонкости и главные правила

При работе с экселевской БД существуют свои тонкости и правила, облегчающие весь процесс:

1. Самая первая строчка БД в обязательном порядке должна включать заголовки столбиков.
2. Строки, следующие после первой, обязательно должны быть заполнены данными (или, по крайней мере, иметь минимум одну заполненную содержимым ячейку).
3. В пределах создаваемой БД не стоит активировать инструмент «Объединение ячейки».
4. Один столбик – один четкий формат. Недопустимо, чтобы столбец сразу включал в себя и текстовое, и числовое содержимое.
5. Табличные ячейки, расположенные вокруг БД, должны пустовать.
6. У диапазона БД обязательно должно быть наименование.

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

Где и как пройти обучение в Экселе

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

Как упростить и ускорить работу в Excel и Google Таблицах и, в частности, научиться тонкостям создания и заполнения БД, можно узнать при прохождении дистанционных обучающих курсов. Освоив тонкости Excel, можно перейти даже на удаленную работу. Большинство компаний заинтересовано в постоянных внештатных сотрудниках.

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

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

Источник: effecom.ru

Создание базы данных в excel по клиентам с примерами и шаблонами

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

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

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

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

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

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

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

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

1.2. Как выполнить поиск, изменение и удаление записей.

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

Форма данных служит не только для создания БД, но и для поиска нужной записи, для ее редактирования или для дальнейшего удаления ненужной записи.

Если в БД сравнительно немного записей, поиск

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

— установить табличный курсор где-либо в БД;

— выполнить последовательность команд Данные – Форма
;

— нажимать кнопку Далее
в появившейся форме или клавишу Enter, пока не появится нужная запись, или воспользоваться полосой прокрутки справа от списка имен полей, щелкая на кнопке со стрелкой вниз в нижней ее части ;

— для перемещения к предыдущей записи нажать Shift
+
Enter
> или щелкнуть на стрелке вверху полосы прокрутки;

— для перемещения к первой записи нажать Ctrl
+
PgUp
> или передвинуть бегунок прокрутки в самый верх полосы прокрутки;

— для перехода на последнюю запись нажать Ctrl
+
PgDn
> или передвинуть бегунок полосы прокрутки в самый низ;

— для перехода по БД на 10 записей вперед нажать PgDn
> , на 10 записей назад – PgUp
> .

Для поиска нужной записи в большой БД следует использовать форму данных и специальные критерии поиска

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

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

— Щелкнуть на первой записи .

— Выбрать Данные – Форма
.

— Щелкнуть на кнопке Критерии
в появившейся форме, после чего очистятся все поля формы.

— Ввести требуемые критерии поиска в соответствующие поля формы.

— Нажать кнопку Далее
или клавишу Enter
, чтобы начать поиск.

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

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

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

Для редактирования

значения поля в текущей записи необходимо перейти в него, нажимая клавиши Tab
> или Shift
+
Tab
> (или с помощью мыши) , и ввести новое значение. Для очистки поля целиком выделить его и нажать клавишу Del
>.

Для удаления

записи из БД щелкнуть на кнопке Удалить
в окне формы данных. При этом, однако, следует помнить, что невозможно восстановить удаленную таким образом запись с помощью команды Отменить
. Поэтому Excel выдаст окно предупреждения с таким сообщением:« Запись, выведенная на экран, будет удалена »
. Можно подтвердить свое решение об удалении записи, щелкнув на кнопке ОК
, или отменить, щелкнув на кнопке Отмена
.

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

Для добавления

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

Источник: knotes.ru

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции.

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

Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.

Рассмотрим, как создать сводную таблицу в Excel.

Создание сводной таблицы в Excel

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

В целом требований немного, но их следует знать.

Обновление данных в сводной таблице Excel

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.

Источник: statanaliz.info