Файл: Лекция Электронная таблица Excel, основные возможности 3 1 Мастер функций MicrosoftExcel, простые вычисления 3.docx

	B
…	…
35	Цвет
36	Синий
37	Белый

В ячейку D40 введем формулу:

=ДМИН(A1:C9;C1; В35:В37)

Ответ равен 5. Эта формула просматривает записи о транспортных средствах¸ выделяет строки со значением в поле Цвет «Синий» или «Белый», определяет минимальное число по полю «Число пассажиров» в этих записях.
Функция БДСУММ – функция баз данных, определяющая сумму чисел в столбце поле в базе_данных, удовлетворяющее критерию.

Общий вид: БДСУММ(база_данных; поле; критерий).

Работает так: проверяет каждую строчку базы_данных на соответствие критерию, среди нужных строк смотрит значение в столбце поле и находит сумму выбранных чисел.

Пример

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

Для расчета создадим критерий, который отображен ниже.

	A	B
41	Наименование транспортного средства	Цвет
42	Самолет	Синий
43	Поезд	Белый

В ячейку D45 введем формулу:

=БДСУММ (A1:C9;C1; A41:В43)

Ответ равен 5. Эта формула просматривает записи о транспортных средствах¸ выделяет строки со значениями синий самолет или белый поезд, определяет сумму по полю «Число пассажиров» в этих записях.
Функция ДСРЗНАЧ – функция баз данных, определяющая минимальное число в столбце, определенном параметром поле, в базе_данных, удовлетворяющее критерию.

Общий вид: ДСРЗНАЧ(база_данных; поле; критерий).

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

---

Пример

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

Для расчета создадим критерий, который отображен ниже.

	A
45	Наименование транспортного средства
46	Самолет
47	Поезд

В ячейку D50 введем формулу:

=ДСРЗНАЧ(A1:C9;C1; A45:А47)

Ответ равен 241,5. Эта формула просматривает записи о транспортных средствах - самолет или поезд¸ выделяет строки со значениями «Самолет» или «Поезд» в поле «Наименование транспортного средства», определяет среднее арифметическое по полю «Число пассажиров» в этих записях.
Контрольные вопросы

1. 
   Какие логические функции вы знаете?
   
2. 
   Как записать вложенные функции?
   
3. 
   Как задаются критерии в функциях базы данных?
   
4. 
   Какие функции базы данных вы знаете?
   

---

Лекция 3. Построение графиков и диаграммв Excel, фильтрация баз данных, создание сводных таблиц

План
3.1 Последовательность построения диаграмм, режимы работы с диаграммой.

3.2 Редактирование диаграмм.

3.3 Фильтрация БД.

3.4 Создание сводных таблиц.

3.1 Последовательность построения диаграмм, режимы работы

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

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

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

Любой график и диаграмма в Excel состоят из следующих элементов:

• 
  непосредственно элементов графика или диаграммы (кривых, столбцов, сегментов),
  
• 
  области диаграммы,
  
• 
  градуированных осей координат,
  
• 
  области построения
  
• 
  легенды.
  

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

---

1. 
   выделить диапазон ячеек А1:С9;
   
2. 
   выполните команду меню Вставка → раздел Диаграммы, выбрать нужный вид диаграммы;
   
3. 
   на экране появится новая лента Работа с диаграммами, состоящая из 3-х вкладок: Конструктор, Макет, Формат.
   
4. 
   выбрать требуемый вид графика или диаграммы, для нашей задачи лучше всего подойдет график с маркерами (рисунок7).
   

Рисунок7 - Выбор типа графика

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

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

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

Рисунок8 –График по пассажирским перевозкам
Если курсор не поставить на таблицу или не выделить ее, то на листе Excel появится чистый график. Тогда его надо заполнить данными. Для этого выделяем чистый график → появится панель Работа с диаграммами с пунктами Конструктор, Макет и Формат→ меню Конструктор→ раздел Данные→ кнопку Выбрать данные (рисунок 9)


Рисунок 9 –Кнопка Выбрать данные
Отобразится окно выбора данных для графика. В нем имеется поле «Выбор данных для диаграммы». В конце поля необходимо нажать на кнопку выбора диапазона (рисунок 10).

---

Риcунок 10 –Кнопка выбора диапазона
Окно выбора данных примет сокращенный вид. Далее следует выделить мышкой на листе Excel таблицу с данными полностью с подписями строк и столбцов, как показано на рисунке ниже, и снова нажать на кнопку выбора диапазона данных (рисунок 11).

Рисунок11 –Кнопка выбора диапазона данных
Окно выбора данных развернется до полного вида, и Вы увидите, что поле «Выбор данных для диаграммы» наполнено некоторым значением. В принципе, это поле можно заполнять и вручную, но выделение мышью ячеек на листе проще и нагляднее. Подписи таблицы распределились по строкам и столбцам. Если Вас что-то не устраивает, то их можно отредактировать вручную нажатием на кнопкуИзменить для горизонтальной или вертикальной оси соответственно (рисунок 12). Но в большинстве случаев автоматическое заполнение данных исправлять нет необходимости. Нажмите ОК.

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

---