НТВ плюс 

Эксель практические задания для студентов. Практикум на тему Excel

Цель работы: формирование практических навыков работы с электронными таблицами MS Excel 2010

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

Оборудование: компьютерный класс, программное обеспечение – MS Excel 2010.

Практическая работа №1
«Создание и форматирование таблицы»

Цель работы: Научиться форматировать таблицу с помощью команды Формат ячейки.

  • Создайте новый файл. Присвойте первому листу имя земля и составьте таблицу по образцу (шрифт Arial, размер 14):

2. Установите формат данных. Выделите ячейку С2 (установите в ней курсор) правой кнопкой мыши и в контекстом меню выберите Формат ячеек…. Во вкладке Число выберите формат Числовой , число десятичных знаков – 0. Нажмите ОК. В ячейке С2 напечатайте 149600000.

Аналогично выделите ячейку С3 и установите формат Числовой , число десятичных знаков – 0. В ячейке С3 напечатайте 384400.

Выделите ячейку С4 и установите формат Время . В ячейке С4 напечатайте 23:56:04.

Выделите ячейку С5 и установите формат Числовой , число десятичных знаков – 3. В ячейке С5 напечатайте 365,256.

Выделите ячейку С6 и установите формат Числовой , число десятичных знаков – 1. В ячейке С6 напечатайте 29,8.

3. Выполните форматирование таблицы.

Объединение ячеек. Объедините диапазон ячеек A1:С1 (ячейки A1, В1, С1). Для этого левой кнопкой мыши выделите указанные ячейки и в контекстном меню выберите Формат ячеек… вкладка Выравнивание . Установите флажок в строке объединение ячеек .

Выравнивание в ячейке. Выберите в строке по горизонтали в раскрывающемся списке – по горизонтали значение по центру .

В строке по вертикали в раскрывающемся списке – по центру .

Измените ширину и высоту ячейки А1 с помощью левой кнопки мыши.

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

Таблица примет вид

Установка границ ячейки . Выделите ячейки А2:С6. В контекстном меню выберите Формат ячеек вкладка Границы . Установите внешние и внутренние границы.

Готовая таблица примет вид.

4. Сохраните таблицу в своей папке под именем таблицы .

Практическая работа №2
«Построение диаграмм»

Цель работы: Научиться создавать и форматировать диаграммы

Задание 1.

1. Откройте файл таблицы . Присвойте второму листу имя Меха и составьте таблицу по приведенному образцу

2. Выделите диапазон данных А2:Вll, включая заголовки строк и столбцов

3. Выберите вкладку Вставка, группу команд Диаграмма, выберите тип диаграммы – Гистограмма – Объемная гистограмма – Гистограмма с группировкой .

4. Изменение названия диаграммы.

После выделения диаграммы будет активирована линейка команд Работа с диаграммами. Выберите во вкладке Макет – Название диаграммы – Над диаграммой. Кликните на диаграмме по названию диаграммы, сотрите старое название и напечатайте новое Носкость лучших по качеству мехов . Диаграмма примет вид

5. Форматирование диаграммы.

Щелкните правой кнопкой мыши на свободном месте области диаграммы и выберите в контекстном меню команду Формат области диаграммы. Установите понравившиеся вам параметры оформления. Примерный результат

Задание 2.

1. Перейдите на следующий лист и присвойте ему имя Пещеры. Создайте таблицу по приведенному образцу:

2. Постройте диаграмму. Примерный вид диаграммы

Задание 3.

1. Перейдите на следующий лист и присвойте ему имя График

2. Постройте диаграмму типа График и отформатируйте диаграмму (для создания подписей по осям и подписей данных используйте вкладку Работа с диаграммами – Макет). Примерный вид диаграммы

Практическая работа №3
«Автозаполнение таблицы»

Цель работы: научиться заполнять ячейки таблицы с помощью функции Автозаполнение.

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

Задание 1.

1. Откройте файл таблицы автозаполнение .

2. Выполните автозаполнение числами . В ячейке А2 напечатать число 1, а в ячейке А3 – число 2. Выделить ячейки А2 и А3. Перетащить маркер заполнения левой кнопкой мыши до ячейки А7.

3. Заполните ячейки днями недели . В ячейке В1 напечатать Понедельник. Перетащить маркер заполнения левой кнопкой мыши до ячейки F1.

4. Заполните остальные ячейки и выполните форматирование таблицы по образцу.

Задание 2.

1. Откройте файл таблицы . Перейдите на новый лист и дайте ему имя температура.

2. Используя функцию Автозаполнение, создайте таблицу по образцу.

3. Сохраните файл.

Практическая работа №4
по теме «Вычисления в MS Excel 2010»

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

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

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

Задание 1.

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

2. Оформите лист для расчета площади прямоугольника по образцу

3. Установите для ячеек В2, В3, В4 числовой формат (один знак после запятой).

4. В ячейку В2 введите число 6, в ячейку В3 введите число 7.

5. Площадь прямоугольника вычисляется в ячейке В4. Установите в нее курсор. Для того, чтобы вычислить площадь квадрата надо значение длины первой стороны прямоугольника умножить на значение второй стороны прямоугольника, т.е. значение ячейки В2 умножить на значение ячейки В3. Введите в ячейку В4 формулу. Для этого

  • напечатайте знак = ;
  • кликните левой кнопкой мыши по ячейке В2;
  • напечатайте знак умножения *;
  • кликните левой кнопкой мыши по ячейке В3;
  • нажмите клавишу Enter.

В ячейке отобразится результат вычисления по формуле =В2*В3, число 42,0.

6. Измените значение в ячейке В2, посмотрите что изменилось. Измените значение в ячейке В3, посмотрите что изменилось.

Задание 2.

периметр квадрата.

2. Оформите лист для расчета периметра квадрата по образцу

3. Введите в ячейку B2 любое число

4. Введите в ячейку В3 формулу для расчета периметра.

5. Посмотрите результат.

Задание 3.

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

2. Известно количество информации в байтах. Оформите лист для расчета количества информации в остальных единицах измерения информации.

Задание 4.

1. Создайте новый лист и присвойте ему имя география.

2. Оформите лист для расчета по образцу и заполните пустые клетки таблицы.

Литература.

  1. Васильев А.В. Работа в электронных таблицах: практикум / А.В.Васильев, О.Б.Богомолова. – М.:БИНОМ. Лаборатория знаний, 2007. – 160 с.
  2. Златопольский Д.М. 1700 заданий по Microsoft Excel / – СПб.: БХВ-Петербург, 2003 – 544 с.

Технология выполнения задания:

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

2.Для того чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить и сразу же (не дожидаясь появления столь необходимого нам в процессоре Word текстового курсора) "писать".

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

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

Зафиксировать данные можно одним из способов:

    • нажать клавишу {Enter};
    • щелкнуть мышью по другой ячейке;
    • воспользоваться кнопками управления курсором на клавиатуре (перейти к другой ячейке).

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

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

Выделите целиком тот столбец таблицы, в котором расположено введенное вами название дня недели.
Каков заголовок этого столбца?
Выделите целиком ту строку таблицы, в которой расположено введенное вами название дня недели.
Какой заголовок имеет эта строка?
Определите сколько всего в таблице строк и столбцов?
Воспользуйтесь полосами прокрутки для того, чтобы определить сколько строк имеет таблица и каково имя последнего столбца.
Внимание!!! Чтобы достичь быстро конца таблицы по горизонтали или вертикали, необходимо нажать комбинации клавиш: Ctrl+→ - конец столбцов или Ctrl+↓ - конец строк. Быстрый возврат в начало таблицы - Ctrl+Home.
Выделите всю таблицу.
Воспользуйтесь пустой кнопкой.

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

6.Выделите ячейку D5; F2; А16 .
Какой адрес имеет ячейка, содержащая день недели?

7.Определите количество листов в Книге1 .

Вставьте через контекстное меню Добавить–Лист два дополнительных листа. Для этого встаньте на ярлык листа Лист 3 и щелкните по нему правой кнопкой, откроется контекстное меню выберите опцию Добавить и выберите в окне Вставка Лист. Добавлен Лист 4. Аналогично добавьте Лист 5. Внимание! Обратите внимание на названия новых листов и место их размещения.
Измените порядок следования листов в книге. Щелкните по Лист 4 и, удерживая левую кнопку, переместите лист в нужное место.

8.Установите количество рабочих листов в новой книге по умолчанию равное 3. Для этого выполните команду Сервис–Параметры–Общие.

Отчет:

  1. В ячейке А3 Укажите адрес последнего столбца таблицы.
  2. Сколько строк содержится в таблице? Укажите адрес последней строки в ячейке B3 .
  3. Введите в ячейку N35 свое имя, выровняйте его в ячейке по центру и примените начертание полужирное.
  4. Введите в ячейку С5 текущий год.
  5. Переименуйте Лист 1

Задание 1.

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

Математические:

  • СУММ - сумма аргументов;
  • ПРОИЗВЕД - произведение аргументов;
  • СУММПРОИЗВ - сумма произведений соответствующих массивов.

Статистические:

  • СРЗНАЧ - среднее арифметическое аргументов;
  • МАКС - максимальное значение из списка аргументов;
  • МИН - минимальное значение из списка аргументов;
  • СЧЕТЕСЛИ - подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.
  1. Заполнить таблицу (5-7 строк). Имеющиеся в шапке таблицы данные (года, месяцы, дни недели) заносить с помощью автозаполнения.
  2. Оформить таблицу с помощью обрамления, добавить заголовок, расположив его по центру таблицы. Шапку таблицы выполнить в цвете (шрифт и фон), полужирным шрифтом.
  3. Переименовать лист книги по смыслу введенной информации.
  4. Добавить в начало таблицы столбец "№ п\п" и заполнить его автоматически.
  5. Выполнить соответствующие вычисления.

1. Спланируйте расходы на бензин для ежедневных поездок из п. Половинка в г. Урай на автомобиле. Если известно:
- расстояние м/д населёнными пунктами в км. (30 км. в одну сторону)
- расход бензина (8 литров на 100 км.)
- количество поездок в месяц разное (т.к. разное количество рабочих дней.)
- цена 1 литра бензина (n рублей за литр.)
- ежемесячный прогнозируемый рост цены на бензин - k% в месяц
Рассчитайте ежемесячный и годовой расход на бензин. Постройте график изменения цены бензина и график ежемесячных расходов.

2. Представьте, что вы директор ресторана. Общий месячный фонд заработной платы - 10000$. На совете акционеров было установлено, что:
- официант получает в 1,5 раза больше мойщика посуды;
- повар - в 3 раза больше мойщика посуды;
- шеф-повар - на 30$ больше...

1. Рассчитайте еженедельную выручку зоопарка, если известно:
- количество проданных билетов каждый день
- цена взрослого билета - 15 руб.
- цена детского на 30% дешевле чем взрослого. Постройте диаграмму (график) ежедневной выручки зоопарка.

2. Подготовьте бланк заказа для магазина, если известно:
- продукты(хлеб, мука, макаронные изделия и т.д., не менее 10 наименований)
- цена каждого продукта
- количество заказанного каждого продукта
Рассчитайте на какую сумму заказано продуктов. Усовершенствуйте бланк заказа, добавив скидку (например 10%), если стоимость купленных продуктов будет более 5000 руб. Постройте диаграмму (гистограмму) стоимости...

1. Найти решение уравнения вида kx + b = 0, где k, b - произвольные постоянные.

2. Сахарный тростник содержит 9% сахара. Сколько сахара будет получено из 20 тонн сахарного тростника?

3. Школьники должны были посадить 200 деревьев. Они перевыполнили план посадки на 23%. Сколько деревьев они посадили?

4. Из 50 кг. семян, собранных учениками, 17% составили семена клена, 15% - семена липы, 25% - семена акации, а стальное - семена дуба. Сколько килограмм...

Практические работы в MS E xcel

Лабораторный практикум предназначен для практического изучения раздела, расчеты в «Электронных таблицах MS Excel - 2007» в рамках дисциплины «Информационные технологии в профессиональной деятельности» студентами второго курса различных специальностей ГБОУ СПО Политехнический колледж №42 г. Москва.

Практикум состоит из четырех практических работ по основным темам применения MS E xcel в расчётах, ориентирован в основном на студентов, обучающихся по специальностям «Экономика и бухгалтерский учет (по отраслям)», « Операционная деятельность в логистике » и «Монтаж и техническая эксплуатация промышленного оборудования (по отраслям)». Некоторые темы практических работ могут использовать в обучении и студенты других специальностей.

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

  1. Практическая работа

Тема: «Организация расчетов в MS Excel »

Целью данной практической работы является освоение технологии организации таблиц в MS Excel , а именно, копирование, форматирование ячеек, формирование границ, представление данных и организация простых формул расчетов. На Рис.1 представлена таблица, в которой столбец А организован посредством копирования содержимого ячейки A 4 (дата 01.04.13) вниз до требуемой ячейки, столбцы B и C заполнены исходными данными, также с использованием копирования и последующей правки значений, столбец D , создан через организацию формулы в ячейку D 4 (в строке формулы, показан вид формулы) и последующим её копированием вниз.

Рис.1

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

Рис.2

Варианты заданий по теме « Организация расчетов в MS Excel »

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

Задание 2 . Создать таблицу по заданию 2. Столбец организовать через копирование ячеек.

Задание 3 . Создать таблицу по заданию 3. Столбец организовать следующим образом с начало заполнить значение 1,0 в ячейку I 4 и 1,1 в ячейку I 5, затем выделить диапазон ячеек, состоящий из ячеек I 4, I 5 и выделенный диапазон копировать вниз.

  1. Практическая работа

Тема: «Статистические функции»

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

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

СРЗНАЧ(x 1 ,…,x n )

среднее арифметическое (x 1 +…+x n )/n.

МАКС(x 1 ,…,x n )

максимальное значение из множества аргументов (x 1 ,…,x n )

МИН(x 1 ,…,x n )

минимальное значение из множества аргументов (x 1 ,…,x n )

СЧЕТ(x 1 ,…,x n )

количество чисел в списке аргументов

СЧЕТЗ(x 1 ,…,x n )

количество значений в списке аргументов и непустых ячеек

Пример выполнения задания с использованием

статистических функций

На рис 4. Показана таблица продаж товара в магазине.

Рис.4

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

Методические указания к выполнению задания:

Вычислить:

    • выручку от продаж каждого товара;

      общую, среднюю, максимальную, минимальную выручку от продаж всех товаров;

      определить общее количество видов товаров в магазине,

      сколько видов товара продано.

Пример выполнения задания по теме «Статистические функции»

    ввести в ячейку D2 (в первую ячейку столбца «Выручка от продаж») формулу: =B2*C2 («Выручка от продаж»= «Цена»*«Количество продаж»);

    скопировать формулу на весь столбец;

    ввести формулы:

в D5 =СУММ(D2:D4) - суммарная выручка

в D6 =СРЗНАЧ(D2:D4) - средняя выручка

в D7 =МАКС(D2:D4) - максимальная выручка

в D8 =МИН(D2:D4) - минимальная выручка

в D9 =СЧЕТЗ(А2:А4) - количество видов товара

(подсчёт количества непустых значений)

в D10 =СЧЕТ(С2:С4) - количество видов проданных товаров (подсчёт количества числовых значений)

Варианты заданий по теме « Статистические функции»

Задание 1 . Организовать таблицу «Реки ЕврАзии».

Рис.5

Задание 2 . Известен возраст десяти человек, претендующих на вакансии в фирму. Определить максимальный, минимальный, средний возраст претендентов?

Задание 3 . Таблица содержит сведения о сотрудниках фирмы: фамилия, стаж работы. Определить средний, максимальный, минимальный стаж. Сколько всего сотрудников?

  1. Практическая работа

Тема: «Логическая функция ЕСЛИ… »

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

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

Алгоритмический язык

Если условие (логическое выражение)

действие 1

иначе

действие 2

всё-если ;

условие

действие 1

действие 2

Блок-схема

Для построения разветвления в MS Excel существует логическая функция ЕСЛИ, структура её такова :

ЕСЛИ значение логического выражения ИСТИНА ,

ТО выполняется оператор 1 ,

ИНАЧЕ выполняется оператор 2 .

Рис. 5 .

Пример задания аргументов функции ЕСЛИ

(нахождение максимального значения из двух чисел)

Для вызова функции ЕСЛИ , надо нажать на кнопку f x «Вставить функцию», находящуюся в строке формулы. Появится Мастер функций в ячейке Категория надо выбрать строку Логические и далее выбрать функцию ЕСЛИ , заполнить три ячейки:

Лог_выражение

Значение_если_истина

Значение_если_ложь

На рис 7. Показан пример применения функции ЕСЛИ Рис 7.

Варианты заданий по теме «Логическая функция ЕСЛИ… »

Задание 1 . В ячейке D 8 поставить значение 800, т.е сделать План = Факт для Серов В.В. Объяните почему не изменился результат?

Задание 2 . Столбец А произвольное число со значением около 1000, столбец В это 2% от числа, столбец С (результат), логическая функция ЕСЛИ, при условии, если число больше или равно 1000, то результат будет = число + 2%, иначе = число – 2%. На рис 8, отражена таблица.

Рис 8_1.

Задание 3 . Столбец Е – первое число, столбец F – второе число, столбец G (результат), формируется следующим образом, если число1 больше числа2, то результат будет их сумма, иначе результат будет их разность. На рис 8_2, отражена исходная таблица с результатом.

Рис 8_2.

  1. Практическая работа

Тема: «Гистограммы, графики»

Целью данной практической работы является освоение технологии представления данных в виде диаграмм в MS Excel . Для формирования гистограмм требуется наличие исходных данных, далее в зависимости от версии MS Office , выбираете меню Вставка и нужный вид гистограммы (графика). Перед вставкой диаграммы рекомендуется находиться в любой ячейке исходной таблицы с данными. Рис 9_1.

На следующем рисунке Рис 9_2. сформирована диаграмма – график функций

y = sin (x ), y = cos (x ), y = x 2 (парабола). Для формирования графиков, требуется столбец значений по X . Значения сформированы от -6, 28 до 6,28 с шагом 0,1 Столбцы для формирования sin (x ), cos (x ) выбраны через вставку функции. Столбец для параболы организован по формуле. Рис 9_2.

Варианты заданий по теме «Гистограммы, графики»

Задание 1 . Организовать круговую диаграмму, по данным Рис 9_1.

Задание 2 . Организовать график функции y = x ^3 (кубическая парабола).

Рис 9_3

Задание 2 . Организовать изменения курса доллара по отношению к руб.

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



Задание 1 : Отсортируйте щенков по стоимости.

Фильтр

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



Задание 2а : выберите всех далматинов.


Чтобы снять автофильтр, надо снять галочку со строки меню Автофильтр.


Можно задавать более сложные условия отбора, например, отобрать всех сеттеров. В выставке участвуют английские и ирландские сеттеры. Значит, нам нужно отобрать всех собак, в названии породы которых СОДЕРЖИТСЯ слово «сеттер».




Задание 2б : выберите всех собак, относящихся к группе сеттеров.

Итоги

Создайте лист Итоги
Теперь нам интересно узнать, сколько представителей разных пород приехало на выставку, и какова средняя стоимость щенка каждой породы.
Для всех этих действий, при которых мы сначала объединяем щенков в группы (по породам), а потом в КАЖДОЙ из них находим либо количество, либо среднее значение, либо другой параметр, нам понадобится такая операция Excel как подведение итогов .


Подведение итогов выполняется в три шага.
1. ОБЯЗАТЕЛЬНО нужно отсортировать щенков ПО ТОМУ ПРИЗНАКУ, по которому мы хотим объединять их в группы (с помощью Сортировки). В данном случае их нужно отсортировать по породе.
2. Выделяете все данные ВМЕСТЕ с заголовками столбцов и в меню Данные выбираете пункт Итоги , у вас открывается диалоговое окно Промежуточные итоги .


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


Задание 3 : Сосчитайте с помощью Итогов количество щенков каждой породы.

Диаграмма

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


Шаг 1 . Подготовка данных
Свернем таблицу, оставив только строки с итогами. Слева на полях напротив таблицы с итогами вы можете видеть рамочки с «плюсиками». Эти рамочки отмечают границы групп. Если кликнуть мышью на «плюсик», то группа свернется и останется только строка с итогом. Вот так:
было:





Итак, мы свернули всю таблицу. Теперь переходим к следующему шагу.


Шаг 2 . Вставка диаграммы.
Так же как и в Word, вставка диаграммы в Excel осуществляется через меню Вставка (пункт Диаграмма ). В открывшемся диалоговом окне вам предложат выбрать тип диаграммы. Для разных задач используются разные диаграммы. В нашем случае лучше всего подойдет круговая: она отображает долю разных значений в общей сумме.


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


Этап ввода данных самый ответственный! Небольшая неточность может привести к тому, что диаграмма будет отображаться некорректно.
1.В поле Диапазон на вкладке Диапазон данных вы указываете ячейки с данными (цифры + подписи) Их можно просто выделить мышью на рабочем поле, и они автоматически занесутся в ячейку.



2. Теперь будьте внимательны! На следующей вкладке Ряды надо заполнить три поля: 1) в поле Имя вы говорите, как будет называться диаграмма; 2) в поле Значения вы вставляете ячейки (выделяя их мышью на рабочем листе) с ЧИСЛОВЫМИ ЗНАЧЕНИЯМИ, по которым рисуется диаграмма; 3) наконец, в поле Подписи категорий вы указываете ячейки с подписями, которые пойдут в легенду диаграммы.



Завершите вставку диаграммы. Разместите ее на том же листе Итоги .


Шаг 3 . Настройка диаграммы.
Теперь нужно настроить внешний вид диаграммы. Если кликать мышкой на разные элементы диаграммы (заголовок, легенду, сектора, область построения и т.п.), то вокруг них появится прямоугольник выделения.




Так же как и в Word, в контекстном меню появится пункт Формат… (Формат легенды, Формат заголовка, Формат области построения, Формат подписи данных и т.п.). В диалоговых окнах формата вы можете настроить цвет, тип заливки и линий, формат шрифта, подписи. Иными словами, довести до блеска внешний вид вашей диаграммы.
Например, так:



Дополнение: добавить проценты рядом с секторами можно в окне Формат рядов данных (когда выделены все цветные сектора).


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


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

Сводные таблицы

Лист Сводная таблица
На выставке судьи выставляют щенкам оценки за экстерьер (внешний вид) и за дрессировку.
Каждый судья оценивает каждую собаку. Все оценки заносятся по порядку в одну таблицу. Но при взгляде на эту таблицу сложно оценить, кто же победил!

Повторение. Функция ЕСЛИ

Определите чемпионов и суперчемпиона выставки. Если сумма баллов у собаки больше или равна 20, то собака - чемпион, а если максимальная из всех участников выставки, то - суперчемпион.