Задание № 3. Организация таблиц.
Цель : Знакомство с организацией вычислений в таблицах.
Темы: Работа с группами листов. Использование «формулы массива». «Автовычисление», «Автоформатирование». Влияющие и зависимые ячейки.
1. Пользуясь методом группового заполнения листов, создайте на трех листах нового документа таблицу, приведенную на рис.5.1, введя данные в диапазон В4:F8. Дайте листам имена "Таб1", "Таб2", "Таб3".
2. Научитесь использовать различные приемы заполнения ячеек формулами.
2.1. В диапазоне G4:G8 запишите формулы для вычисления суммарной нагрузки по группам, пользуясь формулой массива.
2.2. В диапазоне В10:F10 запишите формулы для вычисления суммарной нагрузки по видам нагрузки, пользуясь буфером обмена (ввести формулу, вычисляющую суммарную нагрузку по лекциям в ячейку B10, затем воспользоваться командами Главная – Буфер обмена – Копировать и Главная – Буфер обмена – Вставить, предварительно выделив диапазон вставки).
Рис.5.1
2.3. Запишите формулу для суммирования нагрузки по строкам в ячейку G9.
2.4. Запишите формулу для суммирования нагрузки по столбцам в ячейку G10.
2.5. Запишите формулу для вычисления процентного содержания нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H4).
2.6. Скопируйте данную формулу в диапазон H5:H8, пользуясь автозаполнением.
2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).
|
|
|
2.9. Заполните аналогичными формулами диапазон C11:F11, пользуясь командой Главная – Редактирование – Заполнить вправо.
3. Пользуясь автовычислением, определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.
4. Активизируйте режим ручного пересчета формул (Office – Параметры Excel).
4.1. Несколько раз измените значения в таблице и выполните ручной пересчет.
5. Отформатируйте таблицу на листе "Таб2" по образцу, представленному на рис.5.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H4:H8 и В11:F11).
5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.
5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.
5.3. Отформатируйте таблицу на листе "Таб3", пользуясь функцией автоформатирования.
Рис.5.2
6. Пользуясь командой Формулы – Зависимости формул, выявите влияющие и зависимые ячейки для ячейки G 9.
7. Пользуясь "объемной" формулой =СУММ(Таб1:Таб3!G9), вычислите сумму значений в клетках G9 трех листов и зафиксируйте полученный результат в клетке G15 листа "Таб1".
8. Пользуясь командой Главная – Буфер обмена – Вставить – Специальная вставка, уменьшите значения в диапазоне B10:F10 в четыре раза.
|
|
|
9. Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 – это продажи за январь, для С3 – продажи за январь и февраль, для С4 – продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2), помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.
Рис.5.3
10. Предъявите результаты преподавателю.
Задание № 4. Функции.
Цель : Знакомство с использованием функций табличного процессора MS Excel.
Темы: Математические, статистические и логические функции. Функции даты и времени. Функции ссылки и массива. Текстовые функции. Функции для финансовых расчетов.
1. Научитесь пользоваться математическими и статистическими функциями.
1.1.Создайте таблицу, приведенную на рис.6.1.
Рис.6.1
1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.
1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.
|
|
|
2. Научитесь пользоваться логическими функциями.
2.1. Активизируйте второй лист созданной книги.
2.2. Введите таблицу, приведенную на рис.6.2.
2.3. В клетку С2 введите формулу, по которой будет вычислена скидка и скопируйте ее в диапазон С3:С6:
· если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
· в противном случае - 10%.
2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:
· если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
· в противном случае - 2%.
Рис.6.2
2.5. Повторите п.2.3 для следующих условий:
· если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
· если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
· в противном случае - 10%.
2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.
2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.
|
|
|
Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:
- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:
· вывод даты рождения, взятой из соответствующей клетки,
· если же введено неподходящее имя, вывод сообщения: "нет такого имени".
3. Научитесь пользоваться функциями даты и времени, ссылки и массива.
3.1. Активизируйте третий лист книги Имя_6_1.
3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.
3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.
3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).
3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.
3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:
= РАЗНДАТ(С10;СЕГОДНЯ();"y")
3.7. Представьте текущее время, используя функции ТДАТА() и СЕГОДНЯ().
3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]:мм:сс и Общим форматом, а также форматом 13:30. Зафиксируйте результаты и объясните различие.
3.9. Определите номер текущей недели и выведите сообщение:
"Сейчас идет № недели неделя".
3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.
3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.
3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: "Какую стипендию в n-м семестре получил m-й студент?" Значения n-го семестра и фамилия m-го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.
Рис.6.3
4. Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.
4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.
4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.
4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.
Рис.6.4
5. Научитесь использовать текстовые функции.
5.1. Используйте формулу
="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДДДД ДД ММММ ГГГГ \г\.")
Проанализируйте полученный результат и измените аргумент функции ТЕКСТ, применяющий формат.
5.2. Для данных таблицы, приведенной на рис.6.5, используйте функцию ТЕКСТ для получения информации, идентичной записи в ячейке В6. В ячейке В5 текст «Доход равен» и число из ячейки В3 объедините с помощью конкатенации: «Доход равен » & В3. (Обратите внимание, что число при этом не форматируется).
Рис.6.5
6. Научитесь пользоваться функциями для финансовых расчетов.
6.1. Вычислите объем ежемесячных выплат по ссуде, взятой на на срок 4 года, размер ссуды 70 000 руб., процентная ставка составляет 6% годовых. Для вычислений используйте функцию ПЛТ.
6.2. Вычислите общее количество выплат по ссуде размером 70 000 руб. Ссуда взята под 6% годовых. Объем ежемесячных выплат по ссуде 1 643,95 руб. Для вычислений используйте функцию КПЕР.
6.3. Вычислите объем ссуды, которую можно получить на 4 года под 6% годовых, если объем выплат не превышает 1 643,95 руб. Для вычислений используйте функцию ПС.
6.4. Вычислите основную часть выплат по ссуде за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ОСПЛТ.
6.5. Вычислите часть выплат по ссуде, которая идет на выплату процентов за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ПРПЛТ. Просуммируйте результаты вычислений функций ОСПЛТ и ПРПЛТ за соответствующие периоды и сделайте выводы.
7. Предъявите результаты работы преподавателю.
Задание № 5. Диаграммы.
Цель : Знакомство с графическим представлением табличных данных в MS Excel.
Темы: Работа с диаграммами. Использование основных типов диаграмм. Создание и редактирование диаграмм.
1. Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.
Рис.7.1
2. Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.
2.1 Выделите рабочий диапазон таблицы А4:G6, и нажмите клавишу F11 для быстрого построения гистограммы на отдельном листе.
2.2. Познакомьтесь с командами вкладки Работа с диаграммами – Конструктор - Тип и поменяйте гистограмму на нормированную гистограмму и проанализируйте полученный результат, верните прежний тип гистограммы.
2.3. Используя команду Работа с диаграммами – Конструктор – Данные – Строка/столбец, измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.
2.4. Познакомьтесь с экспресс - макетами диаграммы и примените один из них, для возврата используйте команду экспресс – макет 11.
2.5. Снабдите диаграмму элементами диаграммы, перечень которых можно найти на вкладке Работа с диаграммами – Макет. На диаграмме должны быть подписи данных, легенда, название диаграммы, а также названия осей и таблица значений.
2.6. Выберите маркер диаграммы из ряда Факт с наибольшим значением, увеличьте размер шрифта подписи данных этого маркера и измените его заливку. Используйте команду Формат выделенного фрагмента на вкладке Работа с диаграммами - Макет или Работа с диаграммами - Формат.
2.7. Постройте на рабочем поле первого листа аналогичную гистограмму. Обратите внимание на команду Работа с диаграммами – Конструктор – Расположение, которая позволит расположить диаграмму на отдельном листе или непосредственно в текущем.
2.8. Добавьте новую строку в исходную таблицу, в которой будет рассчитано среднее значение между плановыми и фактическими показателями, и отредактируйте гистограмму, указав новый диапазон данных (Работа с диаграммами – Конструктор – Данные – Выбрать данные). Замените тип диаграммы для ряда среднего значения на график и используйте для него вспомогательную ось. Снабдите гистограмму всеми элементами диаграммы (п.2.5) и оформите ее по своему усмотрению. Сохраните книгу.
3. Познакомьтесь с диаграммами разных типов, предоставляемых Excel и расположите их на отдельных листах. Каждый лист должен иметь имя, соответствующее типу диаграммы, расположенной на нем.
3.1.Постройте диаграмму с областями (Area).
3.2.Постройте линейчатую диаграмму (Bar).
3.3.Постройте диаграмму типа график (Line).
3.4.Постройте круговую диаграмму для фактических показателей (Pie).
3.5.Постройте кольцевую диаграмму (Doughnut).
3.6.Постройте лепестковую диаграмму - "Радар" (Radar).
3.7.Постройте точечную диаграмму (XY).
3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).
3.9.Постройте объемную гистограмму (3-D_Column).
3.10.Постройте объемную диаграмму с областями (3-D_Area).
4. Научитесь редактировать диаграммы[1].
4.1. В диаграмме "График" замените тип диаграммы для данных, обозначающих "План", на круговую и назовите лист "Line_Pie".
4.2. Отредактируйте круговую диаграмму, созданную на листе "Pie", так, как показано на рис.7.2.
4.3. Отредактируйте линейные графики так, как показано на рис.7.3.
Рис.7.2 Рис.7.3
4.4. Научитесь редактировать объемные диаграммы.
4.4.1. Установите "поворот" диаграммы вокруг оси Z для просмотра:
· фронтально расположенных рядов (угол 0 о);
· под углом в 30 о;
· под углом в 180 о;
4.4.2. Измените перспективу, сужая и расширяя поле зрения.
4.4.3. Измените порядок рядов, представленных в диаграмме.
5. Предъявите результаты преподавателю.
[1] Оформление надписи "показатели производства" на рис.7.2 производится факультативно.
Дата добавления: 2019-02-26; просмотров: 1490; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!