Сборник практических работ по информатике "Расчеты в электронных таблицах MS Excel-2000"

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

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

Цель занятия.

Изучение информационной технологии органи­зации расчетов в таблицах MS Excel.

Задание 1.1. Создать таблицу подсчета котировок курса дол­лара.

Исходные данные представлены на рис. 1.1.

Порядок работы.

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel) и создайте новую электронную книгу (Файл/Создать).

При стандартной настройке откроются панели инструментов Стандартная и Форматирование. Если этого не произошло, произведите настройку (Сервис/Настройка/Панели инструментов).

2. Изучите назначение кнопок панелей инструментов про­граммы Microsoft Excel («Стандартная» и «Форматирование»), подводя к ним курсор.

Обратите внимание, что ряд кнопок аналогичны кнопкам программы MS Word и выполняют те же функции (Создать, Открыть, Сохранить, Печать и др.)

3.Установите курсор на ячейку А1. Введите заголовок табли­цы «Таблица подсчета котировок курса доллара».

4.Для оформления шапки таблицы выделите третью строку (нажатием на номер строки), задайте перенос по словам ко­мандой Формат/ Ячейки/вкладка Выравнивание/Переносить по словам, выберите горизонтальное и вертикальное выравнивание — «по центру» (рис. 1.2).

5. В ячейках третьей строки, начиная с ячейки A3, введите названия столбцов таблицы — «Дата», «Курс покупки», «Курс продажи», «Доход».

Изменение ширины столбцов производите из главного меню командами Формат/ Столбец/Ширина или перемещением мышью в строке имен столбцов (А, В, С и т.д.).

6. Заполните таблицу исходными данными согласно зада­нию 1.1.

Краткая справка. Для ввода ряда значений даты набе­рите первую дату 01.12.03 и произведите автокопирование до даты 20.12.03 (прихватите левой кнопкой мыши за маркер ав­тозаполнения, расположенный в правом нижнем углу ячейки, и протащите его вниз).

7. Произведите форматирование значений курсов покупки и продажи.

Для этого выделите блок данных, начиная с верхнего левого угла блока (с ячейки В4) до правого нижнего (до ячей­ки С23); откройте окно Формат ячеек командой Формат/Ячейки/вкладка Число и установите формат Денежный, обозначение валюты — «нет». Число десятичных знаков задайте равное 2 (рис. 1.3).

Краткая справка. Первоначально выделяется блок яче­ек — объект действий, а затем выбирается команда меню на исполнение.

Для выделения блока несмежных ячеек необходимо предва­рительно нажать и держать клавишу [Ctrl] во время выделения необходимых областей.

8. Произведите расчеты в графе «Доход» по формуле

Доход = Курс продажи - Курс покупки, в ячейке D4 наберите формулу = С4-В4 (в адресах ячеек используются буквы ла­тинского алфавита).

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

Краткая справка. Для автокопирования формулы выпол­ните следующие действия: подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; ког­да курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу вниз по ячейкам.

Можно произве­сти автокопирование двойным щелчком мыши по маркеру автозаполнения, если в соседней левой графе нет незаполненных данными ячеек.

9. Для ячеек с результатом расчетов задайте формат Финансо­вый (Формат/Ячейки/вкладка Число/формат Финансовый, обозна­чение признака валюты — «р.» — рубли, число десятичных знаков задайте равное 2).

10. Произведите обрамление таблицы (рис. 1.4). Для этого выделите блок ячеек таблицы, начиная от верхнего левого или от нижнего правого угла таблицы.

Откройте окно Обрамление таблиц командой Формат/Ячейки/ вкладка Границы. Задайте бор­довый цвет линий. Для внутренних линий выберите тонкую, а для контура — более толстую непрерывную линию.

Макет ото­бражает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полнос­тью вас удовлетворит.

11. Выделив ячейки с результатами расчетов, выполните за­ливку светло-сиреневым цветом (Формат/Ячейки/вкладка Вид) (рис. 11.5).

12. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню (Формат/Ячейки/вкладка Выравнивание/отображение — Объединение ячеек). Задайте начертание шрифта — по­лужирное, цвет — по вашему усмотрению.

Конечный вид таблицы приведен на рис. 1.6.

13. Переименуйте ярлычок Лист 1, присвоив ему имя «Курс доллара». Для этого дважды щелкните мышью по ярлычку и на­берите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кноп­кой мыши.

Задание 1.2. Создать таблицу расчета суммарной выручки.

Весь материал - в документе.

РАСЧЕТЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ MS EXCEL-2000

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

Тема: ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL

Цель занятия. Изучение информационной технологии органи­зации расчетов в таблицах MS Excel.

Задание 1.1. Создать таблицу подсчета котировок курса дол­лара.

Исходные данные представлены на рис. 1.1.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel) и создайте новую электронную книгу (Файл/Создать).

При стандартной настройке откроются панели инструментов Стандартная и Форматирование. Если этого не произошло, произведите настройку (Сервис/Настройка/Панели инструментов).

2. Изучите назначение кнопок панелей инструментов про­граммы Microsoft Excel («Стандартная» и «Форматирование»), подводя к ним курсор. Обратите внимание, что ряд кнопок аналогичны кнопкам программы MS Word и выполняют те же функции (Создать, Открыть, Сохранить, Печать и др.)

3.Установите курсор на ячейку А1. Введите заголовок табли­цы «Таблица подсчета котировок курса доллара».

4.Для оформления шапки таблицы выделите третью строку (нажатием на номер строки), задайте перенос по словам ко­мандой Формат/ Ячейки/вкладка Выравнивание/Переносить по словам, выберите горизонтальное и вертикальное выравнивание — «по центру» (рис. 1.2).

5. В ячейках третьей строки, начиная с ячейки A3, введите названия столбцов таблицы — «Дата», «Курс покупки», «Курс продажи», «Доход». Изменение ширины столбцов производите из главного меню командами Формат/ Столбец/Ширина или перемещением мышью в строке имен столбцов (А, В, С и т.д.).

6. Заполните таблицу исходными данными согласно зада­нию 1.1.

Краткая справка. Для ввода ряда значений даты набе­рите первую дату 01.12.03 и произведите автокопирование до даты 20.12.03 (прихватите левой кнопкой мыши за маркер ав­тозаполнения, расположенный в правом нижнем углу ячейки, и протащите его вниз).

7. Произведите форматирование значений курсов покупки и продажи. Для этого выделите блок данных, начиная с верхнего левого угла блока (с ячейки В4) до правого нижнего (до ячей­ки С23); откройте окно Формат ячеек командой Формат/Ячейки/вкладка Число и установите формат Денежный, обозначение валюты — «нет». Число десятичных знаков задайте равное 2 (рис. 1.3).

Краткая справка. Первоначально выделяется блок яче­ек — объект действий, а затем выбирается команда меню на исполнение.

Для выделения блока несмежных ячеек необходимо предва­рительно нажать и держать клавишу [Ctrl] во время выделения необходимых областей.

8. Произведите расчеты в графе «Доход» по формуле

Доход = Курс продажи - Курс покупки, в ячейке D4 наберите формулу = С4-В4 (в адресах ячеек используются буквы ла­тинского алфавита).

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

Краткая справка. Для автокопирования формулы выпол­ните следующие действия: подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; ког­да курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу вниз по ячейкам. Можно произве­сти автокопирование двойным щелчком мыши по маркеру автозаполнения, если в соседней левой графе нет незаполненных данными ячеек.

9. Для ячеек с результатом расчетов задайте формат Финансо­вый (Формат/Ячейки/вкладка Число/формат Финансовый, обозна­чение признака валюты — «р.» — рубли, число десятичных знаков задайте равное 2).

10. Произведите обрамление таблицы (рис. 1.4). Для этого выделите блок ячеек таблицы, начиная от верхнего левого или от нижнего правого угла таблицы. Откройте окно Обрамление таблиц командой Формат/Ячейки/ вкладка Границы. Задайте бор­довый цвет линий. Для внутренних линий выберите тонкую, а для контура — более толстую непрерывную линию. Макет ото­бражает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полнос­тью вас удовлетворит.

11.Выделив ячейки с результатами расчетов, выполните за­ливку светло-сиреневым цветом (Формат/Ячейки/вкладка Вид) (рис. 11.5).

12. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню (Формат/Ячейки/вкладка Выравнивание/отображение — Объединение ячеек). Задайте начертание шрифта — по­лужирное, цвет — по вашему усмотрению.

Конечный вид таблицы приведен на рис. 1.6.

13. Переименуйте ярлычок Лист 1, присвоив ему имя «Курс доллара». Для этого дважды щелкните мышью по ярлычку и на­берите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кноп­кой мыши.

Задание 1.2. Создать таблицу расчета суммарной выручки.

Исходные данные представлены на рис. 1.7.

1. Перейдите на Лист 2, щелкнув мышью по ярлыку Лист 2, при этом откроется новый пустой лист электронной книги.

2. На Листе 2 создайте таблицу расчета суммарной выручки по образцу. В ячейке А4 задайте формат даты, как на рис. 1.7 (Формат/Ячейки/вкладка Число/числовой формат Дата, выбе­рите тип даты с записью месяца в виде текста — «1 Май, 2013 г.»). Далее скопируйте дату вниз по столбцу автокопиро­ванием.

3. Наберите в ячейке ВЗ слова «Подразделение 1» и скопи­руйте их направо в ячейки СЗ и D3.

4.Выделите область ячеек В4:Е24 и задайте денежный фор­мат с двумя знаками после запятой. Введите числовые данные.

5. Произведите расчеты в колонке «Е».

Формула для расчета

Всего за день = Отделение 1 + Отделение 2 + Отделение 3, в ячейке Е4 наберите формулу = В4 + С4 + D4. Скопируйте формулу на всю колонку таблицы. Помните, что расчетные форму­лы вводятся только в верхнюю ячейку столбца, а далее они копируются вниз по колонке.

6. В ячейке В24 выполните расчет суммы значений данных колонки «В» (сумма по столбцу «Подразделение 1»). Для вы­полнения суммирования большого количества данных удобно пользоваться кнопкой Автосуммирование (∑) на панели инст­рументов. Для этого установите курсор в ячейку В24 и выпол­ните двойной щелчок левой кнопкой мыши по кнопке X. Про­изойдет сложение данных колонки «В».

7. Скопируйте формулу из ячейки В24 в ячейки С24 и D24 автокопированием с помощью маркера автозаполнения.

8. Задайте линии вокруг таблицы и проведите форматирова­ние созданной таблицы и заголовка.

9. Переименуйте ярлычок Лист 2, присвоив ему имя «Выруч­ка». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать кон­текстного меню ярлычка, вызываемого правой кнопкой мыши.

10 В результате работы имеем электронную книгу с двумя таблицами на двух листах. Сохраните созданную электронную книгу в своей папке с именем «Расчеты».

Дополнительные задания

Задание 1.3. Заполнить таблицу, произвести расчеты и фор­матирование таблицы (рис. 1.8).

Формулы для расчета:

Всего по цеху = Заказ №1 + Заказ №2 + Заказ № 3; Всего = сумма значений по каждой колонке.

Краткая справка. Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирование (∑) на панели инструментов или функцией СУММ. В качестве первого числа выдели­те группу ячеек с данными для расчета суммы.

Задание 1.4. Заполнить таблицу, произвести расчеты и фор­матирование таблицы (рис. 1.9).

Краткая справка. Добавление листов электронной книги производится командой Вставка/Лист.

Формулы для расчета:

Сумма надбавки = Процент надбавки * Сумма зарплаты.

Примечание. В колонке «Процент надбавки» установите процент­ный формат чисел.

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

Тема: ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ В MS EXCEL

Цель занятия. Изучение информационной технологии пред­ставления данных в виде диаграмм в MS Excel.

Задание 17.1. Создать таблицу «Расчет удельного веса доку­ментально проверенных организаций» и построить круговую диаг­рамму по результатам расчетов.

Исходные данные представлены на рис. 17.1, результаты ра­боты — на рис. 17.6.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Про­граммы/ Microsoft Excel).

2. Откройте файл Расчеты, созданный в Практической ра­боте 16 (Файл/Открыть).

3. Переименуйте ярлычок Лист 3, присвоив ему имя «Удель­ный вес».

4. На листе «Удельный вес» создайте таблицу «Расчет удель­ного веса документально проверенных организаций» по образ­цу, как на рис. 17.1.

Примечание. При вводе текстовых данных, начинающихся со знака тире или другого математического знака, сначала нажмите кла­вишу Пробел — признак текстовых данных, а затем — тире и текст (— государственных, — муниципальных и т.д.).

5. Произведите расчеты в таблице. Формула для расчета

Удельный вес = Число проверенных организаций/ Общее число плательщиков.

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

Расчет удельного веса документально пр . •■ мзаций

Рис. 17.1. Исходные данные для задания 17.1

96

Мастер диаграмм (шаг 1 из 41: тип диаграммы

• .: — -1

-.

Z' 1 "* " ■:^=¹⁽:ⁱi

'"■ ' |~й™* * .1 &

Рис. 17.2. Выбор

6. Постройте диаграмму (круговую) по результатам расчетов с использованием мастера диаграмм.

Для этого выделите интервал ячеек Е7:Е11 с данными рас­чета результатов и выберите команду Вставка/Диаграмма.

Мастер диаграмм [шаг 3 из 41: параметры диаграммы

Рис. 17.4. Задание подписей значений круговой диаграммы

Мастер диаграмм [шаг 4 из 4): размещение диаграммы

1717 ■- i---.r..-:«: ['"■■■''■■"■"

.iqb^S -*" НКбЖ^ГГЯ,

" "'I

Рис. 17.5. Задание размещения диаграммы

Рис. 17.6. Конечный вид диаграммы задания 17.1

98

На первом шаге работы с мастером диаграмм выберите тип диаграммы — Круговая (Объемный вариант разрезанной круговой диаграммы) (рис. 17.2).

На втором шаге на вкладке Ряд в окошке Подписи категорий укажите интервал ячеек В7:В11 (рис. 17.3).

Третий шаг мастера диаграмм. Введите название диаграммы на вкладке Заголовки; укажите подписи значений на вкладке Подписи данных (рис. 17.4).

Четвертый шаг мастера диаграмм. Поместите диаграмму на имеющемся листе (рис. 17.5).

Конечный вид диаграммы приведен на рис. 17.6.

Задание 17.2. Форматирование диаграммы «Расчет удельного веса документально проверенных организаций».

Порядок работы

1. Сделайте диаграмму активной щелчком мыши по ней, при этом появятся маркеры по углам диаграммы и серединам сторон.

2. Мышью переместите диаграмму под таблицу, измените размеры диаграммы (мышью за маркеры).

3. Выполните заливку фона диаграммы. Для этого выполните двойной щелчок мыши по области диаграммы. В открывшемся окне Формат области диаграммы (рис. 17.7) выберите желтый цвет заливки и нажмите на кнопку Способы заливки (рис. 17.8).

ВИД \ Шрр:ф1 j CS"-:''T6.d ■

Рис. 17.8. Диалоговое окно Способы заливки

В открывшемся окне Способы заливки на вкладке Градиентная бегунком выберите степень затемнения и укажите тип штрихов­ки Вертикальная, после чего дважды нажмите кнопку ОК.

4. Отформатируйте легенду диаграммы (окошко в правой ча­сти диаграммы). Щелчком мыши сделайте область легенды ак­тивной, двойным щелчком вызовите окно Формат легенды. На вкладке Вид нажмите на кнопку Способы заливки. В открывшем­ся диалоговом окне Способы заливки выберите вкладку Тексту­ра, укажите вид текстуры Серый мрамор и нажмите кнопку ОК (рис. 17.9).

5. Заштрихуйте один сектор (дольку) круговой диаграммы. Для этого выделите одну дольку (выполните на дольке диаг­раммы два одинарных щелчка, при этом маркеры должны переместиться на дольку). Двойным щелчком по выделенной дольке вызовите диалоговое окно Формат элементов данных, выберите цвет и нажмите на кнопку Способы заливки. В от­крывшемся окне Способы заливки на вкладке Узор выбери­те диагональную штриховку и дважды нажмите кнопку ОК (рис. 17.10).

6. Проведите форматирование подписей данных (значений 34%, 8% и т.д.). Для этого выполните двойной щелчок мыши по одному из численных значений подписей данных и в от­крывшемся окне Формат подписей данных на вкладке Шрифт установите: полужирный курсив — 14 пт., гарнитура шрифта — Anal Cyr (рис. 17.11).

7. Увеличьте область диаграммы. Для выполнения этого фор­матирования выполните щелчок мыши в центре «слоеного пи-

100

!

^ГГ~„ ,--' = 5 ~~ "" Г" \ ^зор I Г ii»

Белы

ч -

Рис. 17.9. Задание текстуры фона легенды

Способы заливки

Г I Т""¹ i Г r~:""i "•: i П "П"

-.-, i,.....

ГТ'Т-

J

,.■)■■ :г\--р

---

_

•[ : 1,:у_й ■•! '.- •:-,■ ; .. .... '

Рис. 17.10. Задание штриховки элемента данных

рога» диаграммы, что приведет к активизации области постро­ения диаграммы. Измените размеры области построения диаг­раммы мышью за угловые маркеры.

Конечный вид диаграммы приведен на рис. 17.12.

8. Скопируйте созданную диаграмму (после выделения диаг­раммы используйте команды Правка/Копировать, Правка/Вста-

Рис. 17.11. Задание формата подписей данных диаграммы

г™—

20%

ш

8%

34%

_,

В - муниципальных:

Z1 - индивидуально-'частных: '

□ - с иностранными инвестициями:

С £(' 'ГИ- ОрГ ill. i4ⁱⁿ¹

Рис. 17.12. Конечный вид круговой диаграммы

9. Измените вид диаграммы на гистограмму. Для этого сде­лайте диаграмму активной щелчком мыши, далее щелчком пра­вой кнопкой мыши по области диаграммы вызовите Свойства диаграммы, выберите команду 7мл диаграммы и укажите тип — Гистограмма. Обратите внимание на произошедшие изменения в диаграмме.

10. Выполните текущее сохранение файла (Файл/Сохранить).

Задание 17.3. Создать таблицу «Сводка о выполнении плана». Построить график и диаграмму по результатам расчетов.

Исходные данные представлены на рис. 17.13. 102

'а.......... I "з ' с...... ; ::

Сводка о выполнении плана

Рис. 17.13. Исходные данные для задания 17.3

При необходимости добавляются новые листы электронной книги командой Вставка/Лист.

Переименуйте ярлычок Лист 4, присвоив ему имя «Выпол­нение плана».

Расчетные формулы:

% выполнения плана = Фактически выпущено/План выпуска; Всего = сумма значений по каждой колонке.

Выполните текущее сохранение файла {Файл/Сохранить).

Дополнительное задание

Задание 17.4. Создать таблицу «Расчет заработной платы». Построить гистограмму и круговую диаграмму по результатам расчетов.

Данные для построения диаграммы выделяйте при нажатой клавише [Ctrl].

Исходные данные представлены на рис. 17.14.

v]-]с^:d;е:

РАСЧЕТЗАРАьОтаОЙ ПЛАТ:- ЗА ; КВАРТАЛ