Пр6

ПРАКТИЧЕСКАЯ РАБОТА № 6

Тема: MS Excel: расчеты и диаграммы

Цель: Изучить встроенные функции, выполнить построение графика и диаграммы в  MS Ехсеl.

Задание: Провести анализ потребительских способностей постоянных поставщиков.

1.                  Записать в отчет ход выполнения работы.

2.                  Оформить таблицей итоги для построения диаграммы.

3.                  Зарисовать круговую диаграмму.

4.                  Зарисовать круговую  диаграмму с вторичной гистограммой.

5.                  Записать показатели выполненной ручной настройки.

6.                  Записать формулы для вычисления значений.

Ход работы.

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

Рисунок 1 Сокрытие столбцов

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

Рисунок 2 Отсортированный список для построения диаграммы

Постройте объемную разрезанную круговую диаграмму по итоговым затратам поставок.

Рисунок 3 Пример построения круговой диаграммы

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

Дополнительное задание : Лист8ВторичнаяГистограмма. Постройте круговую диаграмму с детальными значе­ниями по одному из клиентов на вторичной гистограмме, выполнив ручную настройку.

Лист7Функции. Скопируйте базу поставок на седьмой лист книги. Посчитайте следующие функции.

Математическая функция:

Определение средних значений:

Функции СРЗНАЧ возвращает среднее значение (среднее арифметическое) диапазона ячеек. Это эквивалентно сумме содержимого всех ячеек диапазона, деленной на количество ячеек. Следующая формула возвращает среднее значение диапазона А1:А100: =СРЗНАЧ(А1:А100). Если в указанном диапазоне находятся пустые ячейки или ячейки содержащие текст, то они не включаются в расчет среднего значения. В Ехсеl предусмотрена также функция МЕДИАНА, которая вычисляет медиану значений диапазона, и функция МОДА, которая возвращает то значение, которое чаще всего встречается в диапазоне. Найдите эти значения с помощью соответствующих функций по столбцам цены и количества поставленных запчастей и затрат на их приобретение.

Рисунок 4 Вычисление по формуле среднего значения

Аналогично найдите ранее определенные показатели затрат с помощью функции СУММ, МИН, МАКС и сравните значения.

Выполните округление до ЦЕЛОГо среднего значения затрат, посмотрите как изменится значение.

Функция даты и времени:

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

Рисунок 5 Вывод даты

Функция СИМВОЛ возвращает отдельный символ, соответствующий заданному в качестве аргумента ANSI-коду. Значения аргумента должны находиться в интервале от 1 до 255. Функция КОДСИМВ выполняет обратное преобразование. Следующая формула возвращает букву "А": =СИМВОЛ(65).Эта функция очень полезна для ввода символов, которые сложно или невозможно ввести с клавиатуры. Вставьте в документ в ячейку А25 символ авторского права "©" , ее ANSI-код равен 169.

Рисунок 6 Вставка символа

Логическая функция:

ЕСЛИ

Эта функция, пожалуй, одна из самых важных. Она наделяет ваши формулы "способностью принятия решений". В функции ЕСЛИ используются три аргумента. Первый аргумент - это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. Второй и третий аргументы - это такие выражения, которые вычисляются в случае, если аргумент принимает соответственно истинное или ложное значение.
В следующем примере формула возвращает строку "Положительный", если значение ячейки А1 больше 0, и "Отрицательный", - в противном случае: =ЕСЛИ(А1>0; "Положительный"; "Отрицательный")
Заметьте, что значение первого аргумента (А1) может принимать логическое значение ИСТИНА или ЛОЖЬ. Однако эта формула имеет один недостаток, который заключается в том, что, когда ячейка А1 пуста или содержит 0, возвращается строка текста "Отрицательный". Для решения подобной проблемы можно воспользоваться вложенной функцией ЕСЛИ, которая будет анализировать это условие. Усовершенствованная формула имеет следующий вид:
=ЕСЛИ (А1>0; "Положительный"; ЕСЛИ А1<0; "Отрицательный"; "Ноль"))
На первый взгляд кажется, что формула выглядит очень сложной, но если вы разобьете ее на части, то увидите, что она довольно проста. Вот как она работает. Если значение в ячейке А1 больше 0, то формула возвращает строку текста "Положительный", и на этом ее работа заканчивается. Если значение в ячейке меньше или равно 0, то анализируется второе условие, которое определяется третьим аргументом: =ЕСЛИ(А1<0; "Отрицательный"; "Ноль")

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

Рисунок 7 Функция ЕСЛИ

Текстовая функция:

Функция ДЛСТР возвращает количество символов в текстовой строке. Например, следующая формула возвращает число 9: =ДЛСТР("Атмосфера")
Если нужно определить длину строки без учета пробелов в начале и в конце строки текста, используется функция ДЛСТР, аргументом которой является функция СЖПРОБЕЛЫ. Например, если вы хотите знать количество символов текста, находящегося в ячейке А1 (без каких-либо пробелов), то вам надо использовать формулу: =ДЛСТР(СЖПРОБЕЛЫ (А1)).

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

Форма отчетности по практической работе:

1. Записать в отчет ход выполнения работы.
2. Оформить таблицей итоги для построения диаграммы.
3. Зарисовать круговую диаграмму.
4. Записать показатели выполненной ручной настройки.
5. Записать формулы для вычисления значений.
6. Вывести на печать итоговую таблицу с результатами всех функций. 

Вопросы для контроля знаний:

1. Объяснить разницу между круговой диаграммой и гистограммой.
2. Объяснить назначение элементов диаграммы: область диаграммы, область построения, точки  и ряды данных, оси категорий и значений, легенда, название, метки данных.
3. Рассказать о способах форматирования диаграмм.
4. Объяснить понятие и назначение функции.
5. Описать формат функции.
6. Объяснить понятие аргумент функции, что может быть аргументом?
7. Объяснить назначение и привести примеры логических функции и описать тип аргумента.
8. Объяснить назначение и привести примеры математических функции и описать тип аргумента.
9. Объяснить назначение и привести примеры текстовых функции и описать тип аргумента.
10. Объяснить назначение и привести примеры функции даты и времени и описать тип аргумента.