41892

Структура документа и ввод данных. Лабораторные работы в MS Excel 2007

Лабораторная работа

Архивоведение и делопроизводство

На втором листе книги расположите таблицу приведенную на рис. На третьем листе книги разместите таблицу приведенную на рис. Занесите информацию о расширениях файлов Excel в Office 2007 в табличную область первого листа книги и запомните эти расширения. После открытия окна "Microsoft Excel" активизируйте справочную систему (F1) и выберите в Обзоре справки Excel пункты Управление книгой – Управление файлами - Общие сведения о новых расширениях имен файлов и XML-форматов Office.

Русский

2013-10-26

610.52 KB

61 чел.

Лабораторные работы в MS Excel 2007

(часть 1 вводная)

Задание № 1. Структура документа и ввод данных. 2

Задание № 2 (Часть 1). Базовые команды. 5

Задание № 2 (Часть 2). Таблицы MS Excel 2007. 6

Задание № 3. Способы адресации. 8

Задание № 4 (Часть 1). Форматирование. 10

Задание № 4 (Часть 2). Условное форматирование. 12

Задание № 5. Организация таблиц. 14

Задание № 6. Функции. 16

Задание № 7. Диаграммы. 20


Задание № 1. Структура документа и ввод данных.

ЦельПервоначальное знакомство с табличным процессором MS Excel  2007.

Темы: Ленточный интерфейс. Структура документа. Ввод данных.
Работа с листами.

1. Создайте на доступном диске (локальном или сетевом) папку для хранения созданных в процессе выполнения практических заданий файлов. Дублируйте результаты на съемных носителях.

2. Пользуясь средствами операционной системы, запустите программу табличного процессора Excel.

2.1. Занесите информацию о расширениях файлов Excel в Office 2007 в табличную область первого листа книги и запомните эти расширения. После открытия окна "Microsoft Excel" активизируйте справочную систему (F1) и выберите в Обзоре справки Excel пункты Управление книгой Управление файлами - Общие сведения о новых расширениях имен файлов и XML-форматов Office.

2.2. Просмотрите основные разделы Обзора справки Excel для дальнейшего использования в процессе выполнения практических заданий.

2.3. Пользуясь справочной системой, просмотрите назначение элементов основного окна Excel.

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

заголовков строк;  строки состояния;

панели ярлычков листов;  кнопок прокрутки ярлычков листов;

полосы прокрутки;  кнопки выделения листа.

3. Познакомьтесь с ленточным интерфейсом Excel 2007.

3.1. Активизируйте кнопку Office, находящуюся в верхнем левом углу окна Excel, и познакомьтесь с открывшимся набором команд. Выполните команду Office – Параметры Excel и познакомьтесь с элементами открывшегося диалогового окна.

3.2. Переместитесь по стандартным ленточным вкладкам: Главная, Вставка, Разметка страниц, Формулы, Данные, Рецензирование, Вид, ? и познакомьтесь с группами команд каждой вкладки.

3.3. Вернитесь на кладку Главная и внимательно рассмотрите ее содержимое. В нижней части вкладки находятся названия групп команд, принадлежащих этой вкладке. Первые четыре группы имеют в правом нижнем углу элемент управления, который открывает список дополнительных команд или опций. Познакомьтесь с ними. Кроме стандартных в строке вкладок могут появиться контекстные вкладки при выборе того или иного объекта в листе.

3.4. Научитесь использовать клавиатуру для выбора команд ленточного интерфейса. Нажмите клавишу <Alt> и воспользуйтесь «горячими клавишами» для выбора команд.

4. Познакомьтесь с процессом ввода данных на лист Excel.

4.1. На втором листе книги расположите таблицу, приведенную на рис.1.1. При вводе данных научитесь перемещаться в таблице следующими способами: использованием клавиш "", "", "", "", манипулятора "мышь", с помощью поля имени или командой Главная – Редактирование – Найти и выделить - Перейти (Ctrl + G).

4.2. На третьем листе книги разместите таблицу, приведенную на рис.1.2, предварительно выделив диапазон А1:В8. (Обратите внимание, что при этом при нажатии на клавишу Enter курсор будет перемещаться только по ячейкам выделенного диапазона).

4.3. Познакомьтесь с автозаполнением ячеек константами, элементами прогрессий и элементами списков.

  1.  На четвертом листе книги (вставить новый лист можно с помощью контекстного меню ярлычков) в ячейку А2 введите константу 21. Установите курсор мыши на маркер автозаполнения и протяните при нажатой левой клавише на три ячейки вправо. Ячейки заполнятся числом 21.
  2.  В ячейку А3 введите ту же константу и проделайте те же действия, но при нажатой правой клавише. После отпускания мыши, выберите в контекстном меню пункт Прогрессия и укажите Арифметическую прогрессию с шагом 1. В ячейках должны появиться элементы арифметической прогрессии 21  22  23  24.
  3.  В ячейку А4 введите произвольную дату. Протяните маркер автозаполнения на несколько ячеек вправо при нажатой правой клавише мыши и в открывшемся контекстном меню выберите: по дням, по месяцам, по годам или по рабочим дням.
  4.  В ячейку А5 введите название месяца январь и, пользуясь маркером автозаполнения, протяните мышь при нажатой левой клавише на несколько ячеек вправо. Ячейки должны заполниться следующими названиями месяцев. Для просмотра стандартных и создания новых списков используйте команду Office - Параметры Excel – Изменить списки.

Рис.1.1 Рис.1.2

4.4. Сохраните файл как книгу Excel с четырьмя листами, без макросов.

4.5. Воспользуйтесь подсказкой табличного процессора Excel и найдите раздел, посвященный использованию клавиш оперативного управления. Скопируйте информацию, полученную из справки на первый лист новой книги. Пример таблицы использования клавиш оперативного управления приведен на рис.1.3. Сохраните новую книгу и научитесь перемещаться по табличной области с помощью клавиатуры и команд (п.4.1).

4.6. Откройте оба созданных файла и расположите их на экране рядом. Сохраните рабочую область. Для этого воспользуйтесь командой Вид – Окно – Сохранить рабочую область. При этом создается файл под названием resume. Закройте оба файла и откройте файл resume. Убедитесь, что рабочая область сохранена.

5. Научитесь работать с листами книги.

5.1. Откройте первую книгу с четырьмя листами.

Рис.1.3

5.2. Создайте новую книгу.

5.3. Расположите окна двух книг рядом (Вид – Окно – Упорядочить все - Рядом) и скопируйте во вновь открытую книгу первый и третий лист исходной книги. Используйте контекстное меню для работы с ярлычками.

5.4. Перенесите с помощью мыши второй лист исходного документа в новый документ и поместите его между первым и вторым листом.

5.5. Активизируйте окно исходного документа (книги) и проверьте правильность выполненного переноса (в книге должно остаться три листа – первый, третий и четвертый).

5.6. Сохраните результат работы под новым именем.

5.7. Дайте трем листам документа краткие имена, раскрывающие содержание таблиц и примените к ним разный цвет заливки. Используйте контекстное меню.

5.8. Удалите из книги с тремя листами первый лист.

5.9. Сохраните результат (книгу с двумя листами).

6. Научитесь выделять ячейки и области.

6.1. Научитесь выделять произвольные области таблицы (ячейку, строки, столбцы, диапазоны ячеек и произвольные комбинации ячеек) для выполнения последующих операций редактирования (копирование, перенос, очистка) над выделенными областями, пользуясь комбинациями управляющих клавиш и мышью (рис.1.3):

выделите всю таблицу (заполненные клетки), пользуясь клавиатурой;

выделите весь лист;

выделите область ячеек от ячейки B6 до верхнего левого угла рабочего листа (клетка А1);

выделите область ячеек от ячейки B6 до нижнего правого угла рабочего листа (клетка XFD1048576);

выделите несмежные области таблицы.

7. Откройте окна всех созданных файлов и расположите их каскадом.

8. Предъявите результаты работы преподавателю.


Задание № 2 (Часть 1). Базовые команды.

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

Темы: Команды редактирования данных ("Копировать", "Вырезать", "Вставить", "Очистить"). Команды удаления и вставки столбцов и строк.

1. Создайте таблицу по образцу, приведенному на рис.2.1, и сохраните файл.

Рис.2.1

1.1. Введите названия столбцов (Строка 1) и данные, расположенные в столбцах B,C,D. (столбец номеров не заполняйте).

1.2. Заполните клетки Е2, F2, E11 соответствующими формулами (=C2*D2, =E2*$A$15 и =сумм(Е2:Е10).

1.3. Научитесь просматривать записи формул (в строке формул) и результаты вычислений (значения) в ячейках таблицы.

1.4. Скопируйте формулы в диапазоны Е3:Е10, F3:F10, пользуясь автозаполнением или копированием через буфер, и проследите за модификацией относительных и абсолютных адресов при копировании.

1.5. Заполните столбец номеров, пользуясь арифметической прогрессией с шагом 1

2. Модифицируйте созданную таблицу.

2.1. Вставьте новый столбец "Коэфф." (ГлавнаяЯчейки) между столбцами "Цена" и "Стоимость". Заполните его данными арифметической прогрессии с начальным значением 0,5 и с шагом 0,1.

2.2. Отредактируйте формулу в столбце "Стоимость" так, чтобы она учитывала значения столбца "Коэфф.", например, для четвертой строки =C4*D4*E4.

2.3. Перенесите две первые значащие строки таблицы (строки 2 и 3) в конец списка изделий.

2.4. Вставьте перед 8-й строкой листа ("Трубы") две новые строки и заполните их произвольными подходящими данными.

2.5. Сохраните книгу.

3. Сформируйте из исходной таблицы новую таблицу, состоящую из столбцов "Номер", "Наименование" и "Цена", пользуясь командами: a) Главная – Редактирование - Очистить и б) Главная – Ячейки - Удалить. Отметьте различие в результатах работы этих команд. Переместите вновь созданную так, чтобы ее левый верхний угол находился в клетке С3, и сохранить ее в новой книге для предъявления преподавателю.

4. Откройте файл с исходной таблицей. Для этого файла откройте новое окно (Вид – Окно – Новое) и поместите два окна рядом (Вид – Окно – Рядом). В первом окне активизируйте первый лист, а во втором – второй. Скопируйте таблицу с первого листа на второй с помощью мыши и с помощью команд буфера обмена Копировать и Вставить. При использовании команд буфера обмена расположите таблицу на втором листе, начиная с ячейкиА17. Сохраните файл с модифицированной книгой.

5. Для ячеек B4, B5 исходного файла создайте примечания (РецензированиеПримечанияСоздать примечание), поясняющие, какие именно изделия (размер, сорт, цвет) приведены в таблице. Посмотрите, каким образом отмечаются ячейки, имеющие примечания и как можно управлять примечаниями.

6. Предъявите созданные книги преподавателю.

Задание № 2 (Часть 2). Таблицы MS Excel 2007.

ЦельЗнакомство с возможностями таблиц - списковMS Excel 

Темы: Создание «таблиц», работа с «таблицами», сортировка и фильтрация с использованием раскрывающихся списков в заголовках столбцов.

1. Заполните диапазон А1:F10 данными по образцу, приведенному на рис.2.2.а, или воспользуйтесь результатами предыдущего занятия и сохраните созданный файл.

1.1. Озаглавьте столбцы.

1.2. Заполните диапазон A2:D10.

1.3. Формулы в диапазон E2:F10 вводить не надо.

1.4. Одну из строк диапазона сделайте дублирующей любую другую строку диапазона.

Рис.2.2.а

Рис.2.2.б

2. Преобразуйте диапазон в таблицу.

2.1. Установите курсор внутрь диапазона.

2.2. Выполните команду Вставка – Таблицы – Таблица и в диалоговом окне Создание таблицы проверьте расположение данных таблицы и нажмите ОК.

После преобразования в таблицу диапазон представлен на рис.2.2.б.

3. Познакомьтесь с контекстной вкладкой Работа с таблицами – Конструктор, которая доступна при переходе к любой ячейке таблицы.

3.1. Убедитесь в возможности прокрутки строк таблицы при сохранении на экране заголовков столбцов таблицы.

3.2. Воспользуйтесь командой Сервис – Удалить дубликаты и проследите за результатом.

3.3 Воспользуйтесь командой Параметры стилей таблиц и предложенными командами-флажками для применения особого форматирования для отдельных элементов таблицы.

3.4. Воспользуйтесь командой Стили таблиц – Экспресс-стили и примените один из них.

3.5. Удалите из таблицы одну из строк.

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

4. Познакомьтесь с особенностями ввода формул в таблицу.

4.1. Добавьте в таблицу еще один столбец справа от столбца Стоимость и озаглавьте его Стоимость 1.

4.2. В произвольную ячейку столбца Стоимость введите вручную формулу, обеспечивающую умножение количества продукции на ее цену, например, в ячейку Е6 может быть введена формула =C6*D6. Обратите внимание на то, что формула распространилась на все остальные ячейки столбца таблицы.

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

Убедитесь в том, что в результате во всех ячейках столбца Стоимость 1 будет записана одинаковая формула =[Количество]*[Цена].

Обратите внимание на Автозаполнение формул – средство, позволяющее выбрать функцию, имя диапазона, константы, заголовки столбцов.

4.4. Дайте имя ячейке А15, в которой находится коэффициент, влияющий на комиссионный сбор, например, komiss. Для этого выберите команду Формулы – Определенные имена – Присвоить имя, предварительно активизируйте ячейку А15. Заполните формулами столбец Комисс. сбор, используя Автозаполнение формул.

Познакомьтесь с управлением именами с помощью Диспетчера имен. Активизируйте его командой Формулы – Определенные имена – Диспетчер имен.

5. Добавьте в таблицу строку итогов, которая содержит обобщающую информацию по данным столбцов Стоимость, Стоимость1 и Комисс. Сбор в виде суммирования содержания этих столбцов. Для этого воспользуйтесь командой Конструктор – Параметры стилей таблиц – Строка итогов . Для столбца Стоимость 1 вычислите итоговое среднее значение.

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

6.1. Отсортируйте таблицу по наименованию продукции (в алфавитном порядке).

6.2. Отсортируйте таблицу в порядке убывания цены на продукцию.

6.3. С помощью фильтрации найдите данные таблицы для бетона и дверей.

6.4. Рассмотрите возможности Текстовых, Числовых фильтров и Фильтров по дате (добавьте в конец таблицы столбец с датами поступления товаров на склад).

7. Предъявите результаты работы преподавателю.


Задание № 3. Способы адресации.

ЦельЗнакомство со способами адресации табличного процессора MS Excel.

Темы: Абсолютная, относительная и смешанная адресация. Адресация с использованием имен.

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

2. Создайте таблицу, приведенную на рис.3.1, заполнив только ячейки A3:E7. Установите размер ячеек в соответствии с образцом, приведенным на рис.3.1.

Рис.3.1 Рис.3.2

3. Запишите формулы, позволяющие выполнить следующие вычисления:

3.1. Сумму всех элементов первой строки (разместить в клетке Н3).

3.2. Сумму всех элементов второй строки (разместить в клетке Н4).

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

3.4. Сумму всех элементов первого столбца разместите в клетке А10.

3.5. Сумму всех элементов второго столбца разместить в клетке В10.

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

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

3.8. В клетке Н12 запишите формулу для вычисления суммы элементов главной диагонали матрицы, которая будет включать все ячейки диагонали (в том числе и ячейку C5), не содержащие данных. Сравните результаты в ячейках Н11 и Н12.

4. В ячейках А11, А12, А13 поместите формулы, позволяющие выполнить следующие вычисления:

4.1. Сумму элементов матрицы, расположенных в блоке клеток от А3 до В4.

4.2. Сумму элементов матрицы, расположенных в блоке клеток от D6 до Е7.

4.3. Разность сумм элементов, расположенных в блоках А3:В4 и D6:Е7.

5. Пользуясь операциями копирования, создайте, начиная с ячейки А20, таблицу, приведенную на рис.3.2.

6. Для вычисления суммы элементов блока клеток А20:С21 запишите в ячейках А25, С25, Е25 и G25 следующие формулы:

в клетке А25 адреса должны быть записаны как относительные;

в клетке С25 адреса должны быть записаны как абсолютные;

в клетке Е25 адреса должны быть записаны с абсолютным указанием строки и относительным указанием столбца;

в клетке G25 адреса клеток должны быть записаны с абсолютным указанием столбца и относительным указанием строки.

7. В ячейках H20, H21, H22 и H23 поместите аналогичные формулы. (Войдите в режим редактирования, находясь в ячейке-источнике, скопируйте содержимое источника в буфер, нажмите клавишу <esc>, перейдите в ячейку-приемник и вставьте в нее содержимое буфера).

8. Перейдя в режим отображения результатов, просмотрите вычисленные значения и проверьте их правильность.

9. Скопируйте формулы из строки 25 в строку 27.

Проверьте, как ведут себя относительные и абсолютные адреса клеток.

Просмотрите результаты вычислений по формулам строк 25 и 27.

Проанализируйте результаты.

10. Скопируйте формулы из столбца H в столбец J.

Проверьте, как ведут себя относительные и абсолютные адреса клеток.

Просмотрите результаты вычислений по формулам столбцов H и J.

Проанализируйте результаты.

11.Установите стиль ссылок R1C1 (Office – Параметры Excel – Формулы) и проанализируйте формулы, занесенные в ячейки А25, С25, Е25, G25.

12. Определите имена для областей A20:C21; A22:C23; D20:F21; D22:F23 соответственно как ONE, TWO, THREE, FOUR.

13. Вычислите суммы элементов заданных блоков, используя имена диапазонов. Разместите результаты вычислений в ячейках B30:B33.

14. Используя имена диапазонов, перейдите в области THREE и FOUR и очистите их содержимое.

15. Предъявите результаты работы преподавателю.


Задание № 4 (Часть 1). Форматирование.

ЦельЗнакомство с методами оформления таблиц.

Темы: Форматирование текстовых и числовых данных. Оформление ячеек. Защита данных.

1.Форматирование текста.

1.1. Выравнивание текста.

1.1.1. Создайте на первом рабочем листе таблицу по приведенному образцу (рис.4.1) и сохраните ее в файле Имя_4_1 для дальнейшего использования.

Рис.4.1

1.1.2. Скопируйте таблицу на второй рабочий лист.

1.1.3. Отформатируйте тексты таблицы по образцу, приведенному на рис.4.2. На этом примере научитесь выравнивать текст всеми доступными способами. Перед выполнением этого пункта установите для всего рабочего листа стандартную ширину столбцов и высоту строк (по умолчанию – 8,43 символа и 15 пунктов).

1.1.4. Включите режим автоматической установки ширины столбцов (ГлавнаяЯчейкиФорматАвтоподбор ширины столбца) и посмотрите как изменится внешний вид таблицы. Подстройте параметры таблицы (ширину столбцов и высоту строк) так, чтобы внешний вид таблицы соответствовал рис.4.2. Для выравнивания используйте команды ГлавнаяВыравнивание и вкладку Выравнивание в диалоговом окне Формат ячеек, которое активизируется кнопкой в правом нижнем углу группы команд Главная - Выравнивание.

1.2. Шрифтовое оформление текста.

1.2.1. Воспользовавшись режимом форматирования ячеек (ГлавнаяШрифт), оформите тексты в таблице второго листа так, как представлено на рис.4.3. В таблице на рис.4.3 использованы следующие варианты шрифтового оформления текста: жирный, подчеркнутый, курсив, жирный курсив, перечеркнутый, а также верхний и нижний индексы.

2. Прорисовка границ. 

2.1. Для оформления таблицы (рис.4.3) используйте подчеркивание и границы, используя  команду ГлавнаяШрифтГраницы и вкладку Граница диалогового окна Формат ячеек.

Рис.4.2

3. Заливка и узор.

3.1. Для выделения данных в таблице используйте различные варианты оформления из меню ГлавнаяШрифтЦвет текста, ГлавнаяШрифтЦвет заливки и вкладку Заливка диалогового окна Формат ячеек.

Рис.4.3

4. Форматирование числовых данных.

4.1. Просмотрите все варианты форматирования чисел, предлагаемые табличным процессором Excel, используя вкладку Число диалогового окна Формат ячеек.

4.2. На третьем рабочем листе создайте таблицу, приведенную на рис.4.4. Отформатируйте числовые данные с использованием числовых форматов, как показано на рисунке. Столбец F заполните формулами, вычисляющими отношение значений столбца E ("Продано шт.") к общей сумме, записанной в клетке E15.

4.3. Очистите формат ячеек B4:B5 (ГлавнаяРедактированиеОчиститьОчистить форматы) и объясните полученный результат.

4.4. Отформатируйте ячейку F4, как показано на рис.4.4, и скопируйте созданный формат в диапазон F5:F8 (ГлавнаяБуфер обменаФормат по образцу).

Рис.4.4

5. Защита данных.

5.1. Защитите заголовки строк и столбцов таблицы, приведенной на рис.4.4, и оставьте возможным изменение числовых данных таблицы. Для защиты используйте вкладку Защита диалогового окна Формат ячеек и команду Рецензирование – Изменения – Защитить лист.

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

7. Предъявите результаты преподавателю.

Задание № 4 (Часть 2). Условное форматирование.

ЦельЗнакомство с возможностями условного форматирования таблиц.

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

1. Создайте таблицу, приведенную на рис.4.5.

1.1. Примените к диапазону В3:В14 условное форматирование с помощью набора значков «три сигнала светофора без обрамления», а к диапазону С3:С14 - «пять четвертей».

1.1.1. Активизируйте команду Главная – Стили – Условное форматирование – Наборы значков.

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

1.2. Создайте правило условного форматирования на основе формулы. Отформатируйте только те значения диапазона В3:В14, которые больше 40%, выделив их красной заливкой. Для этого активизируйте команду Главная – Стили – Условное форматирование – Создать правило. В диалоговом окне Создание правила форматирования выберите Использовать формулу и введите формулу =В3>$А$16. Перейдя в диалоговое окно Формат ячеек, установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.

Рис.4.5

2. Создайте таблицу, приведенную на рис.4.6.

2.1. С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.

2.2. Для диапазона В2:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.

2.3. Для диапазона С2:С14 выделите суммы заказов, выше среднего значения и ниже среднего, а также выделите четыре наибольших сумм заказов.

2.4. Вставьте новый столбец справа от столбца С и скопируйте в него столбец сумм заказов, выровняйте значения по правому краю и увеличьте ширину столбца. Примените условное форматирование Гистограммы.

2.5. К диапазону Курьер примените условное форматирование Текст содержит и выделите значение Гермес.

Рис.4.6

3. Предъявите результаты преподавателю.


Задание № 5. Организация таблиц.

ЦельЗнакомство с организацией вычислений в таблицах.

Темы: Работа с группами листов. Использование «формулы массива». «Автовычисление», «Автоформатирование». Влияющие и зависимые ячейки.

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. Пользуясь командой Формулы – Зависимости формул, выявите влияющие и зависимые ячейки для ячейки G9.

7. Пользуясь "объемной" формулой =СУММ(Таб1:Таб3!G9), вычислите сумму значений в клетках G9 трех листов и зафиксируйте полученный результат в клетке G15 листа "Таб1".

8. Пользуясь командой Главная – Буфер обмена – Вставить – Специальная вставка, уменьшите значения в диапазоне B10:F10 в четыре раза.

9. Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 – это продажи за январь, для С3 – продажи за январь и февраль, для С4 – продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2), помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.

Рис.5.3

10. Предъявите результаты преподавателю.


Задание № 6. Функции.

ЦельЗнакомство с использованием функций табличного процессора 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:

  1.  если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
  2.  в противном случае - 10%.

2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:

  1.  если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
  2.  в противном случае - 2%.

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

  1.  если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
  2.  если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
  3.  в противном случае - 10%.

2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.

2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:

- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

  1.  вывод даты рождения, взятой из соответствующей клетки,
  2.  если же введено неподходящее имя, вывод сообщения: "нет такого имени".

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. Предъявите результаты работы преподавателю.


Задание № 7. Диаграммы.

ЦельЗнакомство с графическим представлением табличных данных в 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. Научитесь редактировать диаграммы2.

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. Предъявите результаты преподавателю.

2 Оформление надписи "показатели производства" на рис.7.2 производится факультативно.


 

А также другие работы, которые могут Вас заинтересовать

58266. Эпоха Просвещения 55.5 KB
  Шарль Луи Монтескье выдающийся французский мыслитель энциклопедического склада. Как философ социолог и писатель Монтескье оставил глубокий след в истории прогрессивной мысли. Велика роль Монтескье в деле идейной подготовки Великой французской буржуазной революции.
58267. Як людина отримує інформацію 38 KB
  Мета: Ознайомити учнів з тим, за допомогою яких органів людина сприймає інформацію. Дати уявлення про колір фону в Paint. Розвивати увагу, пам’ять, логічне мислення. Виховувати любов до природи.
58268. Организация бухгалтерского дела и архива 63.5 KB
  Для обобщения различных видов учетных работ в единое целое обеспечения равномерности их выполнения в течение отчетного периода составляется специальный график в котором для подразделений бухгалтерии и отдельных ее работников указывается время представления первичных документов...
58269. Производство продукции из вторичного древесного сырья на лесопромышленном складе 82.5 KB
  Подготовка древесного сырья является важнейшей частью технологического процесса обеспечивающей возможность использования низкокачественной древесины для получения технологической щепы с допускаемой стандартом засоренностью корой и гнилью.
58270. Начало Французской революции. Свержение монархии. Якобинская диктатура 147.5 KB
  И сразу начались затяжные споры по различным вопросам: о способах проверки полномочий депутатов о совместных и раздельных заседаниях о задачах Генеральных штатов о правах третьего сословия завтрашнем дне страны о будущем Франции.
58271. ЕКОНОМІКА ВЕЛИКОБРИТАНІЇ 40 KB
  У самому справі Великобританії можуть вижити тільки виробництва і торгівлі. За винятком вугілля низькосортна залізна руда природний газ і нафта у Великобританії є кілька природних ресурсів. Вугілля була замінована у Великобританії більше 300 років.
58272. Контроль витрат і стимулювання економії ресурсів. Система обліку витрат 199.5 KB
  Контроль витрат є важливою складовою системи управління витратами, без якої неможлива повноцінна реалізація інших її функцій. До основних завдань контролю витрат відносять: моніторинг - систематичне відстежування динаміки витрат і факторів, які на неї впливають...
58274. Ознайомлення з підручником. Предмет. Фігура. Лічба предметів 36 KB
  Мета: ознайомити учнів з підручником Математика; з’ясувати чи вміють діти лічити числа й називати їх; дати уявлення про предмети геометричні фігури; прищеплювати зацікавленість вивченням математики.