Пр7 Выполнение расчетов и диаграмм в MS Ехсеl

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

Тема: Выполнение расчетов и диаграмм в MS Ехсеl.

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

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

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

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

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

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

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

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

                   
Ход работы.

Лист4 Итоги. Вернитесь на четвертый лист книги.

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 41 Вычисление по формуле

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

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

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

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

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

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

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

ЕСЛИ

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

Вставите за Маркой кирпича дополнительный столбец Особенности, в котором для кирпича марки ША-5, ША-8, ША-22, ШБ-5 необходимо указать признак «огнеупорный».

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

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

ЗАМЕНИТЬ

Функция ЗАМЕНИТЬ позволяет заменить символы в тексте. Первый аргумент этой функции – текст, содержащий заменяемую вами строку. Второй аргумент – позиция символа, с которой вы хотите начать замену. Третий аргумент – количество заменяемых символов. Четвертый аргумент – новый текст, который заменит имеющийся. В следующем примере формула возвращает "Альберт Эйнштейн":
= ЗАМЕНИТЬ("Альберт Гор"; 9;3; Эйнштейн)

ПОИСК

Функция ПОИСК позволяет определить позицию в текстовой строке, с которой начинается заданная строка. Эта функция имеет три аргумента. Первый – текст, который нужно найти; второй – строка, в которой ищется текст; третий (необязательный) аргумент – место начала поиска. Если третий аргумент опущен, то Excel начинает поиск с начала текста. В следующем примере предполагается, что в ячейке А1 находится текст "Пушкин Александр Сергеевич". Формула возвращает 7 потому, что первый по счету пробел находится на седьмой позиции в исходной строке.
=ПОИСК(" "; А1; 1)
Чтобы найти второй по счету пробел, вместо третьего аргумента подставим вложенную функцию ПОИСК, к значению которой прибавим 1,
=ПОИСК(" "; А1; ПОИСК(" "; А1; 1) +1)
В следующей формуле для выделения первого слова, ограниченного пробелом, из строки текста, находящегося в ячейке А1, используется функция ЛЕВСИМВ. Например, если в ячейке А1 находится текст "Александр Сергеевич Пушкин", то следующая формула выделит из текста имя "Александр".
=ЛЕВСИМВ(А1; ПОИСК(" "; А1; 1))
Представленная формула имеет явный недостаток: если текст в ячейке А1 не имеет пробелов, то в результате возникнет ошибка. Вот как выглядит усовершенствованный вариант этой формулы, которая выделяет первое слово из строки, находящейся в ячейке А1, даже если эта строка не содержит пробелов:
=ЕСЛИ(ЕОШИБКА(ПОИСК(" "; А1; 1)) ;А1; ЛЕВСИМВ(А1; ПОИСК(" ";А1; 1)))

ПРОПИСН

Функция ПРОПИСН преобразует строчные символы в прописные. Например, если в ячейке А1 находится "Пушкин", то следующая формула возвращает "ПУШКИН":
=ПРОПИСН(А1)

Это еще один оператор ЕСЛИ, который вновь выполняет проверку содержимого ячейки А1. Если проверяемое значение меньше 0, то формула возвращает строку текста "Отрицательный", в противном случае она возвращает строку "Ноль". При необходимости можно использовать несколько вложенных друг в друга операторов ЕСЛИ. Однако не стоит забывать, что, когда уровень вложенности превышает три - четыре, разобраться в формуле становится очень сложно.
Вложенные функции ЕСЛИ используются достаточно часто, поэтому вы должны досконально разобраться в этой методике. Овладев ею, вы сможете создавать очень гибкие формулы.
Показан пример использования функции ЕСЛИ для расчета комиссионных от продаж. В данном примере обычный процент комиссионных продаж составляет 5,5%. Однако если продавец превышает определенный процент продаж, то комиссионные составляют 6,25%. Приведенная ниже формула находится в ячейке С6. В ней для принятия решения о проценте комиссионных, который зависит от объема продаж, используется функция ЕСЛИ:
=ЕСЛИ(В6=Норма_продаж; В6*Ставка_премиальных; В6*Ставка_комиссионных)

СЕГОДНЯ

ДЕНЬНЕД

Функция ДЕНЬНЕД определяет день недели указанной даты. Она имеет два аргумента – дату и код запроса, определяющий тип результата (второй аргумент необязательный). Например, если в ячейке А1 содержится значение 25.12.97, то следующая формула возвращает 4, что означает, что этот день – четверг:
=ДЕНЬНЕД(А1; 2)
Вы можете отформатировать ячейки с датами так, чтобы дни недели выводились на экран в привычной текстовой форме, а не в виде цифр. Для этого в списке числовых форматов выберите опцию Все форматы и в поле Тип введите шаблон ддд(для отображения аббревиатур дней недели) или дддд (для отображения полного названия дней недели).

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

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

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

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