В базе данных ms excel возможно ли наличие пустых строк между записями

Основные
термины

• Список
  (база данных) – это таблица на рабочем
  листе, состоящая из строк с однотипными
  данными и организованная по принципу
  базы данных.

• Поле
  – столбец таблицы, элементами которого
  являются однотипные данные (реквизиты).
  Каждый столбец базы данных является
  полем. Поле данных обязательно должно
  иметь заголовок, имя.

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

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

• Диапазон
  – это диапазон ячеек, содержащий
  записи базы данных.

Основные
правила создания списка

• На
  отдельном рабочем листе создавайте
  только одну базу данных.

• Не
  вставляйте в базу данных пустые строки.
  Если пустая строка вставлена между
  именами полей и данными, то Excel не
  определит имена полей. Пустые строки
  между записями воспринимаются Excel как
  конец БД.

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

• Имена
  полей вводите в ячейки только одной
  (первой) строки базы данных. Имена,
  занимающие несколько строк, не
  распознаются Excel.

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

• Не
  используйте в качестве имен полей даты,
  формулы или пустые ячейки.

• Теоретически
  размер базы данных в Excel ограничен
  размерами листа (65536 строк и 256 столбцов).
  Но если вы хотите использовать для базы
  данных это пространство полностью, то
  лучше воспользуйтесь реляционными
  базами данных, например Access
  или Visual FoxPro,
  так как Excel не обработает такой объем
  информации.

Основные правила подготовки информации для создания базы данных

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

• Обдумайте
  содержание вашей базы данных, ее
  назначение, размеры, название, расположение
  на рабочем листе.

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

• Определите
  ширину поля для каждого элемента записи,
  число полей, количество записей.

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

• Предусмотрите
  возможность ввода новых записей, полей
  и элементов базы данных.

• Не
  вводите непонятные или не принятые
  сокращения слов.

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

В

следующих упражнениях создадим базу
данных Зарплата (рисунок 1), а
затем будем работать с ней.

Рисунок
1.
Начальный
вариант базы данных Зарплата

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

Упражнение
1. Порядок ввода данных в список Зарплата

1. Для
   создания БД Зарплата введите сначала
   в ячейку В1 справочную информацию
   Тарифная ставка, а в ячейку С1
   введите значение тарифной ставки,
   равное 1100 рублям. В дальнейшем при
   изменении тарифной

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

1. Введите
   название таблицы (см. рисунок 1) в ячейку
   В3.

2. В
   ячейки В5:В14 (поле Ф.И.О.) введите
   фамилии с инициалами имен и отчеств
   работников (см. рисунок 1).

3. В
   ячейки А4:F4
   введите имена полей базы данных (см.
   рисунок 1). Выделите имена полей жирным
   шрифтом, выровняйте по центру с переносом
   по словам.

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

Упражнение
2. Заполнение ячеек последовательными
значениями

1. В
   ячейку А5 введите число 1.

2. Выделите
   ячейки А5:А14.

3. Выполните
   команды Правка/Заполнить/Прогрессия.
   Появится диалоговое окно Прогрессия.

4. В
   секции Расположение выберите по
   столбцам.

5. Выберите
   тип прогрессии Арифметическая
   прогрессия.

6. В
   поле Шаг: введите 1.

7. Щелкните
   ОК. Ячейки заполнятся числами от 1
   до 10.

Если
в ячейке БД находятся данные, состоящие
из нескольких слов, разделенных пробелами
(поле Ф.И.О. в БД Зарплата), то
выполнение сортировки данных по фамилиям
становится затруднительным. Поэтому
для фамилий, имен, отчеств лучше
предусмотреть отдельные поля в базе
данных или разделить уже введенные
данные на составные части.

Упражнение
3. Выделение составных частей имени

1. Вставьте
   столбец после столбца В.

2. Выделите
   поле Ф.И.О. в БД Зарплата. Выберите
   Данные/Текст по
   столбцам…. Появится диалоговое окно
   Мастер текстов – шаг 1.

3. Установите
   селектор с разделителями. Щелкните
   Далее.

4. Во
   втором окне мастера текстов выберите
   символом разделителем пробел.
   Щелкните Далее.

5. В
   третьем окне мастера текстов установите
   формат общий. Нажмите кнопку
   Готово.

6. В
   ячейку В4 введите новое имя поля
   Фамилия, в ячейку С4 введите имя
   поля И.О.

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

Упражнение
4. Проверка данных при вводе

1. Выделите
   ячейки D5:D14.

2. Выберите
   Данные/Проверка….
   Появится диалоговое окно Проверка
   вводимых значений, которое содержит
   три вкладки.

3. На
   вкладке Параметры откройте выпадающий
   список Тип данных и выберите
   Целое число.

4. Откройте
   выпадающий список Значение
   и выберите между.

5. В
   поле Минимум наберите 1, в поле
   Максимум введите 25.

6. Щелкните
   на вкладке Сообщение для ввода.

7. В
   текстовом поле Заголовок:
   напечатайте Число дней. В текстовом
   поле Сообщение: напечатайте
   Вводите числа от 1 до 25.

8. Перейдите
   на вкладку Сообщение об ошибке.
   Установите переключатель Выводить
   сообщение об ошибке.

9. Откройте
   выпадающий список Вид.
   Выберите формат сообщения Останов. В
   поле Заголовок: напечатайте
   Дни. В поле Сообщение:
   наберите текст сообщения —
   Нажмите клавишу Повторить
   и введите правильное значение от 1 до
   25.

10. Нажмите
    ОК. Диалоговое окно закроется.

11. Теперь
    вводите данные в поле Число дней.
    Введите в ячейку D5
    число 240. С помощью проверки данных
    исправьте ошибку.

12. Для
    отмены установленной проверки данных
    нажмите кнопку Очистить
    все в диалоговом окне Проверка
    вводимых значений.

В
следующих упражнениях заполним формулами
вычисляемые поля базы данных
Зарплата.

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

В
поле Начислено нужно ввести формулу
вычисления заработной платы =D5*$C$1.
Заработная плата в данной задаче
вычисляется умножением числа дней,
отработанных за месяц (ячейки D5:D14),
на величину тарифной ставки. Значение
тарифной ставки содержится в ячейке
С1. При копировании формулы в ячейки
D5:D14
это значение, очевидно, должно
оставаться неизменным. Значит, в формулу
в ячейке Е5 должна быть введена
абсолютная ссылка на ячейку С1.
Абсолютную ссылку можно задать с помощью
функциональной клавиши [F4].

Упражнение
5. Использование клавиши [F4]
для ввода абсолютной ссылки

1. Активизируйте
   ячейку Е5 и наберите (но не вводите!)
   с клавиатуры или с помощью панели формул
   (см. упражнение 22) формулу =D5*C1.

2. Нажмите
   клавишу [F4].
   Ссылка C1 в ячейке D5 станет
   абсолютной: $C$1.

3. Снова
   нажмите клавишу [F4].
   Абсолютным станет номер строки: C$1.

4. Нажмите
   клавишу [F4].
   Абсолютным станет имя столбца: $C1.

5. Нажмите
   клавишу [F4].
   Ссылка С1 станет относительной: С1.

6. Еще
   раз нажмите клавишу [F4].
   Ссылка C1 в ячейке D5 станет
   абсолютной: $C$1.

7. Теперь
   подтвердите ввод формулы в ячейку Е5.

8. Скопируйте
   содержимое ячейки Е5 в ячейки Е6:Е14.

Упражнение
6. Вычисление совокупного налога

1. Для
   заполнения формулами вычисляемого
   поля Удержано в ячейку F5
   введите формулу =0,13*Е5 (тринадцать
   процентов совокупного налога от
   начисленного заработка).

2. Скопируйте
   эту формулу в ячейки F6:F14.

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

Упражнение
7. Использование имен полей в вычисляемых
полях

1. Активизируйте
   ячейку G5.

2. Введите
   с клавиатуры формулу с именами полей
   =Начислено-Удержано.

3. Скопируйте
   эту формулу в диапазон ячеек G6:G14.

Округлите
все результаты расчетов до двух знаков
после запятой. Сохраните базу данных
Зарплата в своей папке. Результат
всех действий по созданию базы данных
показан на рисунке 2.

Рисунок
2. Подготовленная
к работе база данных Зарплата

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Добрый день!

Темой этой статьи, как вы поняли, станет создание собственной базы данных. Для тех, кто по опытнее, слова база данных сразу вызовет ассоциацию с MS Access, 1C, Oracle, SQL, СУБД FoxPro и другие, с которыми могла свести судьба и работа. Сразу соглашусь с вами, что это очень хорошие, мощные и достойные базы данных, которые позволяют обрабатывать преогромнейшие массивы данных, но такие удовольствия и стоят соответственно совсем не копейки. В случае если ваш бизнес маленький вам всё равно нужно упорядочить, отсортировать и автоматизировать свои данные что позволит улучшить анализ и принимаемые решения, что зачастую являются главными условиями выживаемости в экономических реалиях. Также небольшие базы данных можно создавать и для своих нужд или структурирования информации, например, база данных книг (которые у вас есть или прочитали или хотите прочитать), ведения личного финансового учёта и прочее.

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

Если вас интересует вопрос, почему именно в MS Excel я решил создавать базу данных, то думаю, что это не секрет что это самая распространённая и доступная программа из тех, что имеется у вас на компьютере, да и с этой задачей сможет справиться, на 5 с плюсом. Так как инструментов у нее предостаточно, это и формулы, и возможность создания интерфейса с использованием VBA, и формирование дашбордов, и инструменты для связывания диаграмм и ячеек, инструменты которые могут производить поиск и создание обновляемых динамических отчётов.

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

• База данных – это обыкновенная двумерная таблица в Excel, которая была создана при соблюдении определенных правил;
• Поле (или столбик) – содержит в себе информацию об определенном признаке или значении для записей, во всей базе данных (определяется в шапке базы данных);
• Запись (или строка) – состоит из нескольких или множества признаков, или значений, которые могут охарактеризовать только один объект вашей базы данных;
• Расширяемая база данных – это созданная таблица, куда постоянно производится добавление новых данных или записей (строк) вашей информации. Неизменными остаются всегда количество полей и название.

Всё вышенаписанное является очень важным, так как незнание механизмов работы приведет к ошибке, поэтому это нужно знать и помнить!

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

Правила создания базы данных в Excel

1. Обязательно! Без исключений! Первая строка в вашей базе данных должна содержать название заголовков полей (столбцов);
2. Каждая запись (строка) базы данных обязана содержать ячейку с заполненными данными (никаких пустых строк);
3. Любое объединение диапазонов ячеек запрещено на всей таблице базы данных;
4. Каждое поле (столбик) должно, обязательно, содержать в себе только один определенный тип данных, это либо текстовые значения, либо числовые или значения времени;
5. Пространство возле вашей базы данных обязательно должно быть пустым;
6. Всему диапазону вашей базы данных необходимо присвоить имя;
7. Укажите, что диапазон вашей базы данных является списком;
8. Рекомендовано создание и ведение базы данных на отдельном листе.

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

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

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

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

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

• Страница №1 будет содержать весь набор данных для формы ввода данных, а точнее для формирования данных выпадающего списка, что создаст удобство в использовании, унифицирует данные и застрахует меня от большинства ошибок;
• Страница №2 будет служить мне формой ввода данных в мою базу данных, это позволит мне быстро и почти в автоматическом режиме с помощью макроса добавлять новые записи мне в базу данных;
• Страница №3 будет хранить собственно базу данных, никаких активных работ я здесь не буду делать, чтобы не повредить ее целостности;
• Страница №4 это будет структурированный результат на основе сводной таблицы, которая будет удобно и в нужной форме отбирать записи с базы данных и предоставлять их мне.

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

Следующим шагом станет создание формы ввода для нашей базы данных. В ней я перечислил все те данные, которые мне нужно внести в мою базу. Порядковый номер записи уже стоит, так как он определяется формулой: =СЧЁТЗ(Библиотека!B3:B9949)+1 которая считает записи и выводит номер следующей. В ячейки выделенные синим цветом я буду вводить данные вручную, а в белых ячейках я создам выпадающий список, это позволит мне водить одинаковые значения и избавится от несовпадения данных и ошибок. Введенные данные будут формировать строку A16:L16, которую с помощью макроса, прикреплённого к кнопке, буду переносить в свою библиотеку. Создаю кнопку запуска макроса, на вкладке «Разработчик» в блоке «Элементы управления» нажимаем пиктограмму «Вставить» и в выпадающем меню выбираем элемент «Кнопка», рисуем ее на нашем поле, где будет удобно ее использовать и подписываем ее. Теперь вызываем редактор VBA для написания выполняемого макроса по внесению записи в базу данных: Создаем для нашей кнопки отдельный обыкновенный модуль, выбрав пункт «Insert», потом «Module»: Вставляем в модуль наш макрос «Add_Books»:

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

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

Внешний вид рабочей области программы – таблица. А реляционная база данных структурирует информацию в строки и столбцы. Несмотря на то что стандартный пакет MS Office имеет отдельное приложение для создания и ведения баз данных – Microsoft Access, пользователи активно используют Microsoft Excel для этих же целей. Ведь возможности программы позволяют: сортировать; форматировать; фильтровать; редактировать; систематизировать и структурировать информацию.

То есть все то, что необходимо для работы с базами данных. Единственный нюанс: программа Excel — это универсальный аналитический инструмент, который больше подходит для сложных расчетов, вычислений, сортировки и даже для сохранения структурированных данных, но в небольших объемах (не более миллиона записей в одной таблице, у версии 2010-го года выпуска ).

Структура базы данных – таблица Excel

База данных – набор данных, распределенных по строкам и столбцам для удобного поиска, систематизации и редактирования. Как сделать базу данных в Excel?

Вся информация в базе данных содержится в записях и полях.

Запись – строка в базе данных (БД), включающая информацию об одном объекте.

Поле – столбец в БД, содержащий однотипные данные обо всех объектах.

Записи и поля БД соответствуют строкам и столбцам стандартной таблицы Microsoft Excel.

Если Вы умеете делать простые таблицы, то создать БД не составит труда.

Создание базы данных в Excel: пошаговая инструкция

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

Как создать базу данных клиентов в Excel:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Шаблон для менеджера, позволяющий контролировать результат обзвона клиентов. Скачать шаблон для клиентской базы Excel. Образец:
2. Простейший шаблон.Клиентская база в Excel скачать бесплатно. Образец:

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

Создание базы данных в Excel

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

Шаг 1. Исходные данные в виде таблиц

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Итого у нас должны получиться три «умных таблицы»:

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

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

Шаг 2. Создаем форму для ввода данных

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

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW) . Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY) .

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP) . Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation) , указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

Аналогичным образом создается выпадающий список с клиентами, но источник будет уже:

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

Шаг 3. Добавляем макрос ввода продаж

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

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) . В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer — Insert — Button) :

После того, как вы ее нарисуете, удерживая нажатой левую кнопку мыши, Excel сам спросит вас — какой именно макрос нужно на нее назначить — выбираем наш макрос Add_Sell. Текст на кнопке можно поменять, щелкнув по ней правой кнопкой мыши и выбрав команду Изменить текст.

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

Шаг 4. Связываем таблицы

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

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations) . В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

Шаг 5. Строим отчеты с помощью сводной

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

Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6. Заполняем печатные формы

Еще одной типовой задачей любой БД является автоматическое заполнение различных печатных бланков и форм (накладные, счета, акты и т.п.). Про один из способов это сделать, я уже как-то писал. Здесь же реализуем, для примера, заполнение формы по номеру счета:

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX) .

Создание базы данных в Excel и функции работы с ней

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

Как сделать базу данных в Excel, чтобы не было удобно не только хранить, но и обрабатывать данные: формировать отчеты, строить графики, диаграммы и т.д.

Пошаговое создание базы данных в Excel

Для начала научимся создавать БД с помощью инструментов Excel. Пусть мы – магазин. Составляем сводную таблицу данных по поставкам различных продуктов от разных поставщиков.

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

Примечание. Данную таблицу можно скачать в конце статьи.

По базе видим, что часть информации будет представляться в текстовом виде (продукт, категория, месяц и т.п.), а часть – в финансовом. Выделим ячейки из шапки с ценой и стоимостью, правой кнопкой мыши вызовем контекстное меню и выберем ФОРМАТ ЯЧЕЕК.

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

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

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

Теперь заполняем таблицу данными.

Важно! При заполнении ячеек, нужно придерживаться единого стиля написания. Т.е. если изначально ФИО сотрудника записывается как Петров А.А., то остальные ячейки должны быть заполнены аналогично. Если где-то будет написано иначе, например, Петров Алексей, то работа с БД будет затруднена.

Таблица готова. В реальности она может быть гораздо длиннее. Мы вписали немного позиций для примера. Придадим базе данных более эстетичный вид, сделав рамки. Для этого выделяем всю таблицу и на панели находим параметр ИЗМЕНЕНИЕ ГРАНИЦ.

Аналогично обрамляем шапку толстой внешней границей.

Функции Excel для работы с базой данных

Теперь обратимся к функциям, которые Excel предлагает для работы с БД.

Работа с базами данных в Excel

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

Выделяем шапку таблицы и во вкладке ДАННЫЕ нажимаем ФИЛЬТР (CTRL+SHIFT+L).

У каждой ячейки в шапке появляется черная стрелочка на сером фоне, куда можно нажать и отфильтровать данные. Нажимаем ее у параметра ПРИНИМАЛ ТОВАР и снимаем галочку с фамилии КОТОВА.

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

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

Можно произвести дополнительную фильтрацию. Определим, какие крупы принял Петров. Нажмем стрелочку на ячейке КАТЕГОРИЯ ПРОДУКТА и оставим только крупы.

Вернуть полную БД на место легко: нужно только выставить все галочки в соответствующих фильтрах.

Сортировка данных

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

К примеру, мы хотим отсортировать продукты по мере увеличения цены. Т.е. в первой строке будет самый дешевый продукт, в последней – самый дорогой. Выделяем столбец с ценой и на вкладке ГЛАВНАЯ выбираем СОРТИРОВКА И ФИЛЬТР.

Т.к. мы решили, что сверху будет меньшая цена, выбираем ОТ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ. Появится еще одно окно, где в качестве предполагаемого действия выберем АВТОМАТИЧЕСКИ РАСШИРИТЬ ВЫДЕЛЕННЫЙ ДИАПАЗОН, чтобы остальные столбцы тоже подстроились под сортировку.

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

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

Сортировка по условию

Нам нужно извлечь из БД товары, которые покупались партиями от 25 кг и более. Для этого на ячейке КОЛ-ВО нажимаем стрелочку фильтра и выбираем следующие параметры.

В появившемся окне напротив условия БОЛЬШЕ ИЛИ РАВНО вписываем цифру 25. Получаем выборку с указанием продукты, которые заказывались партией больше или равной 25 кг. А т.к. мы не убирали сортировку по цене, то эти продукты расположились еще и в порядке ее возрастания.

Промежуточные итоги

И еще одна полезная функция, которая позволит посчитать сумму, произведение, максимальное, минимальное или среднее значение и т.п. в имеющейся БД. Она называется ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие ее от обычных команд в том, что она позволяет считать заданную функцию даже при изменении размера таблицы. Чего невожнможно реалиловать в данном случаи с помощью функции =СУММ(). Рассмотрим на примере.

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

Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет 30 аргументов. Первый статический: код действия. По умолчанию в Excel сумма закодирована цифрой 9, поэтому ставим ее. Второй и последующие аргументы динамические: это ссылки на диапазоны, по которым подводятся итоги. У нас один диапазон: F4:F24. Получилось 19670 руб.

Теперь попробуем снова отсортировать кол-во, оставив только партии от 25 кг.

Видим, что сумма тоже изменилась.

Получается, что в Excel тоже можно создавать небольшие БД и легко работать с ними. При больших объемах данных это очень удобно и рационально.

Excel Создать базу данных

• Создать базу данных в Excel

Excel Create Database (Содержание)

• Создать базу данных в Excel
• Как создать базу данных в Excel?

Создать базу данных в Excel

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

Как создать базу данных в Excel?

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

Вы можете скачать этот шаблон базы данных Excel здесь — Создать шаблон базы данных Excel

Ввод данных для создания базы данных Excel

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

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

Я добавил первые несколько идентификаторов сотрудников . Произнесите D01, D02, D03, а затем перетащите оставшиеся до строки 12, используя ручку заполнения . Второй столбец содержит общую информацию о сотруднике, такую ​​как Имя, Фамилия, Назначение и Зарплата. Заполните эту информацию в ячейках вручную в соответствии с вашими данными. Убедитесь, что формат столбца Зарплата применяется ко всем ячейкам столбца (в противном случае эта база данных может вызвать ошибку при использовании).

Ввод правильных данных

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

Смотрите скриншот ниже:

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

Предположим, вы добавили правильную запись, но на 2- й строке после последней строки (т.е. одна строка оставлена ​​пустой). Также не рекомендуется делать это. Это разбивка для ваших данных. Смотрите скриншот ниже:

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

Давайте посмотрим на скриншот ниже:

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

Все строки называются «Запись в базе данных Excel».

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

Каждый столбец — это поле в базе данных Excel

Каждый столбец называется Поле в базе данных Excel. Заголовки столбцов называются именами полей.

Таблица формата

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

• Выберите ячейки от A2 до F12 в электронной таблице.
• Перейдите на вкладку « Главная ».
• Выберите « Формат как таблица» в раскрывающемся меню. Вы можете выбрать собственный стол.

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

Как только вы довольны диапазоном, вы можете выбрать OK. Теперь вы можете увидеть свои данные в табличной форме. Смотрите скриншот, приведенный ниже:

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

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

Расширение базы данных

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

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

Форматирование базы данных

Выделите ячейку A1 до F1 из электронной таблицы.

• Выберите вкладку « Главная »
• На вкладке «Главная страница» перейдите к « Обтекание текстом», а также « Объединение и центрирование».
• Вы также можете изменить цвет заливки. Выберите Цвет заливки. Выберите цвет вашего интереса. Здесь я выбрал зеленый цвет.

Вот как мы создали нашу базу данных в Excel.

Что нужно помнить о создании базы данных в Excel

• Информация об одном предмете должна быть полностью заполнена в одном ряду. Вы не можете использовать несколько строк для добавления разных данных одного и того же элемента в базу данных Excel.
• Поле не должно быть пустым. (Включая заголовки столбцов / название поля).
• Тип данных, введенный в один столбец, должен быть однородным. Например, Если вы вводите данные о зарплате в столбце Зарплата, в этом столбце не должно быть текстовой строки. Аналогично, любой столбец, содержащий текстовые строки, не должен содержать никакой числовой информации.
• База данных, созданная здесь, действительно очень маленький пример. Это становится огромным с точки зрения сотрудников, которые время от времени присоединяются, и становится беспокойным, чтобы поддерживать данные снова и снова со стандартным форматированием. Вот почему рекомендуется использовать базы данных.

Рекомендуемые статьи

Это было руководство по созданию базы данных в Excel. Здесь мы обсудим, как создать базу данных в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

1. Как импортировать данные в Excel?
2. Применение стилей таблиц в Excel
3. Как использовать панель инструментов Excel?
4. Руководство по строкам и столбцам Excel

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

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

База данных в MS Excel это просто список, состоящий из одного или более столбцов.

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

Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться строгих правил:

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

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

На рабочем листе, содержащем список,  выделяются следующие области:

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

Существуют следующие способы ввода данных в список:

• использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды  Данные → Форма;
• ввод данных во вставляемые в список пустые строки, т.е. непосредственно ввод данных;
• использование средства Автоввод и команды Выбрать из списка для ускорения работы;
• использование форм MS Access и дальнейший перенос данных на лист MS Excel;
• использование мастера шаблонов для преобразования рабочего листа в MS Excel в форму;
• применение VBA – соответствующая программа будет предоставлять форму или окно диалога для ввода данных и их последующего помещения в определенные ячейки рабочего листа MS Excel.

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

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

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

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

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

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

На рабочем листе, содержащем список, различают следующие области:

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

К наиболее часто используемым способам ввода данных в список относятся:

а) использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды  Данные → Форма;

б) ввод данных в пустые строки списка, т.е. непосредственный ввод данных;

в) использование средства Автоввод и команды Выбрать из списка для ускорения работы.

Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:

1. Сортировка – выстраивание данных в алфавитном или цифровом порядке по возрастанию и убыванию, выполняется командой Данные → Сортировка; открывается диалоговое  окно Сортировка диапазона, в котором задаются ключи сортировки (столбцы или строки) и порядок сортировки. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля для упорядочивания списка. Пустые клетки всегда помещаются в конце всех данных. В диалоговом окне Параметры сортировки можно задать особый порядок сортировки, например по дням недели.

2. Фильтрация (отбор данных) – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями). Для поиска и фильтрации данных в MS Excel существует 3 средства: форма данных (кнопка Критерии), автофильтр и расширенный фильтр.

Отбор данных с помощью формы данных производится следующим образом: нужно установить указатель ячейки в любое место внутри списка, выбрать команду Данные → Форма, затем нажать кнопку Критерии; в открывшемся окне в необходимых полях ввести критерии поиска, например первую букву в названии (для перехода к записи, удовлетворяющей критерию, следует использовать кнопки Далее или Назад).

Задавая критерии можно пользоваться символами подстановки: * — для замены любого количества символов (например, Ж* может соответствовать таким наименованиям товаров как жакет и жилет), ? – для замены одного символа.

Поиск с помощью автофильтра производится в следующем порядке:

1. Установление указателя ячейки в список данных.
2. Выполнение команды Данные → Фильтр → Автофильтр. Возле каждого поля строки заголовка появятся раскрывающиеся списки в виде кнопки с треугольником.
3. Переход к нужному полю.
4. Выбор необходимого критерия поиска или использование пользовательского автофильтра Условие (больше или равно, не равно и т.д.), который позволяет также задать комплексный критерий, объединяя его компоненты с помощью логического оператора И либо ИЛИ.

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

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

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

Чтобы получить точное соответствие отобранных значений заданному образцу, например текст, следует ввести следующую формулу: =»=текст». При задании критериев можно также пользоваться символами подстановки. При вычислениях Microsoft Excel не учитывает регистр букв.

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

=C7>СРЗНАЧ($C$7:$C$10)

Формула, используемая для создания условия отбора, должна использовать относительную ссылку на заголовок столбца (например, «Реализация») или на соответствующее поле в первой записи. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В данном примере «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.

Чтобы объединить критерии с помощью оператора И, их следует указывать в одной строке. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Брюки» в столбце «Вид изделия», «Костюмная ткань» в столбце «Вид материала» с объемом реализации более 100 руб.

Чтобы объединить критерии с помощью оператора ИЛИ, их следует указывать в различных строках. Так, при наличии для одного столбца двух и более условий отбора необходимо ввести эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Вид изделия» значения «Брюки», «Юбка» или «Бриджи».

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

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, нужно ввести эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Брюки» в столбце «Вид изделия», так и объем реализации, превышающий 300 шт., а также строки по виду изделия «Юбка» с объемом реализации более 100 руб.

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

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

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

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

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

Для ручной группировки данных необходимо выделить первый диапазон, соответствующий создаваемой группе, обратиться к команде Данные → Группа и структура → Группировать. Группирование следует произвести последовательно для всех уровней структуры. В диалоговом окне Группирование необходимо указать, что будет объединяться: строки или столбцы. В диалоговом окне команды Настройка указывается, где будут расположены данные (в строках под данными или в столбцах справа от данных).

2)    Автоматическое подведение итогов:  итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Промежуточные итоги формируются с помощью команды Данные → Итоги. В диалоговом окне указывается, в каком столбце должны отслеживаться изменения данных, после каждого из которых производится выбранная вычисляемая операция. Также указывается, по какому столбцу будут производиться вычисления и где будут располагаться итоги. Перед применением команды к данным следует применить фильтр.

3)    Консолидация данных —  предназначается для обобщения однородных данных и выполняется в том случае, когда нужно подытожить данные, хранящиеся на разных листах или в различных книгах. При помощи функции консолидации для значений из несмежных диапазонов можно выполнить те же операции, что и при автоматическом определении промежуточных итогов: вычислить сумму, минимальное, максимальное или среднее значение и т. д.

4)    Сводные таблицы и диаграммы — представляют собой средства для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, созданных в других приложениях. Сводные таблицы могут использоваться: для обобщения большого количества однотипных данных;  для отбора, группировки или реорганизации данных (с помощью перетаскивания), а также  для построения диаграмм.

Рассмотрим принципы работы с инструментами Сводные таблицы и Консолидация данных.

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

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

В этом случае для расчета итоговых данных составляются обычные или так называемые,  трехмерные формулы — ссылки  вида  ‘Лист1:Лист3’ !D3, которые содержат ссылки на диапазоны, включающие ячейки разных листов. Для их создания следует открыть первый лист из числа выбираемых, нажать клавишу Shift и, щелкая кнопкой мыши, указать остальные листы диапазона (они должны быть расположены подряд). После этого нужно:

1. Выбрать ячейку, в которой должна располагаться формула.
2. Вызвать функцию для выполнения итоговых вычислений, воспользовавшись командой Вставка→Функция, кнопкой Функция или Автосумма (если необходимо просуммировать данные) или строкой формул.
3. Для ввода аргументов функции нужно перейти на лист, где расположены ячейки с исходными данными, выделить нужную ячейку и ввести символ точки с запятой. Операция повторяется для всех листов с исходными данными (на последнем шаге точка с запятой не вводится).

Может получить выражение вида: = СУММ (поставщики!C4;’материал верха’!C5), где поставщики и материал верха – названия листов, С4 и С5 – ячейки листов «Поставщики» и «Материал верха» соответственно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

После вызова мастера появляется диалоговое окно Мастер сводных таблиц и диаграмм — шаг 1 из 3, в котором указываются источник данных для сводной таблицы и тип отчета — сводная таблица или диаграмма.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Однако, если в источнике данных появились новые строки или столбцы, необходимо вернуться к мастеру сводных таблиц и диаграмм, а именно:  вызвать команду Данные → Сводная таблица или открыть список Сводная таблица панели инструментов и выбрать команду Мастер, в результате чего откроется третье диалоговое окно мастера сводных таблиц, щелкнуть на кнопке Назад для возврата ко второму окну мастера и выбрать источник данных заново или нажать клавишу Shift и расширить область выделения, для закрытия окна мастера щелкнуть на кнопке Готово.

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

• Выделить любое поле в сводной таблице, щелкнуть на кнопке Отобразить список полей панели инструментов Сводные таблицы, чтобы вывести на экран список полей источника данных, перетащить требуемое поле из окна в нужную область сводной таблицы.
• Активизировать команду Данные → Сводная таблица, чтобы открыть третье окно мастера, щелкнуть на кнопке Макет, в открывшемся диалоговом окне добавить необходимые поля, а затем закрыть окно и щелкнуть на кнопке Готово.

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

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

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

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

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

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

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

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