Практическая работа по теме «Тема: Электронные таблицы. Microsoft Excel» для ИнЭУ

Цели: Студенты должны знать: окно программы MS Excel, основные элементы MS Excel, операции с ячейками, расчетные операции, основные формулы и функции Excel.

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

Теоретическая часть

Что такое электронная таблица? Когда-то давным-давно, в 1979 году, двое студентов Ден Бриклин и Боб Френкстон, изучавших основы предпринимательства, решили, что выполнение математических расчетов на бумаге с помощью карандаша и калькулятора – пустая трата времени. И вместо того, чтобы в безисходности просиживать над выполнением заданий, как это делали другие студенты, друзья решили придумать нечто этакое и создали на своем компьютере первую программу электронных таблиц (ЭТ). Они назвали свою программу VisiCalc, что означало Наглядный Калькулятор. Идея, лежащая в основе VisiCalc и всех других программ ЭТ, состоит в том, чтобы в одни ячейки ввести числа, а в других ячейках указать, как эти числа должны быть математически преобразованы, и предоставить компьютеру сделать всю сложную работу по вычислению результата, а себе оставить более легкую работу по вводу исходных данных.

Все современные программы ЭТ, ведут свое происхождение от VisiCalc, созданной Деном и Бобом.

Итак, рассматривайте Excel как персональный калькулятор, с помощью которого вы можете выполнять разные расчетные работы, от самых простых, например планирования семейных расходов, до таких сложных, как прогнозирование годовой прибыли крупной компании.

Основные термины и понятия ЭТ

ЭТ состоит из (см. рис.1):

рабочего листа, разделенного на строки и столбцы;

ячеек, образющихся на пересечении строки и столбца;

чисел, служащих для ввода любых числовых значений

текстовых строк, предназначенных для ввода заголовков, описания продуктов, имен клиентов и т.д.;

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

Каждая ячейка ЭТ имеет уникальный адрес ячейки, состоящий из буквы столбца и номера строки. Ячейка, в которой находится табличный курсор, называется активной.

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

Рисунок 1 Рабочий лист Excel

формуле появляется в ячейке рабочего листа, а сама формула отображается в строке формул.

Все формулы в Excel начинаются со знака равенства (=). Знак (=) указывает программе, что нужно определить и вычислить выражение, следующее за этим знаком.

Чтобы задать арифметические операции в простейших формулах, можно использовать комбинации следующих символов:

+ для выполнения сложения;

— для выполнения вычитания;

* для выполнения умножения;

/ для выполнения деления;

^ для возведения числа в степень.

Чаще всего в формулах, создаваемых в Excel, явные числовые значения (такие как числа 10 или 15) не используются. Вместо явных значений в формулах в формулах используются ссылки на ячейки, в которых содержатся эти значения (например ячейки А2 или В3). Причина довольно проста, если вы создадите формулу со ссылками на ячейки, в которых находятся нужные для вычислений значения, то результат вычислений по формуле автоматически изменится после изменения чисел в ячейках.

Самый простой способ для задания формул в ЭТ – это использование кнопки Автосумма å. Кнопка Автосумма å не только автоматически вставляет функцию =СУММ, но и выделяет блок ячеек, которые, по мнению программ, вы собираетесь суммировать. Если программа правильно выделила ячейки, то все что вам остается сделать для создания формулы, — это завершить ввод (нажать Enter).

В Excel отдельные рабочие листы организуются в рабочие книги. Каждая книга может содержать до 256 рабочих листов.

Рабочая книга – основной документ Excel.

Практическая часть

Запустите Excel: Пуск4Программы4Microsoft Excel

Если Вы все сделали правильно, то Excel начнет загружаться, и Вы увидите на экране ее заставку. После завершения загрузки появится экран Excel c чистой рабочей книгой Книга 1.

УПРАЖНЕНИЕ 1. Вставка дополнительных листов в рабочую книгу

Для создания нашей рабочей книги нам потребуется 5 листов. Excel автоматически предлагает три листа. Нам нужно добавить два листа. Для этого:

Щелкните в строке меню пункт Вставка

В появившемся подменю щелкните Лист. Появится Лист 4

Вставьте Лист 5

УПРАЖНЕНИЕ 2: Потренируйтесь в выборе ячеек

Сделайте активной ячейку Е2, щелкнув мышью во 2 строке столбца Е.

Сделайте активной ячейку В6 с помощью клавиатуры управления курсором.

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

Сделайте активной ячейку А1.

УПРАЖНЕНИЕ 3. Ввод заголовка таблицы

В ходе упражнения Вы начнете создавать первый лист рабочей книги — электронную таблицу, которая будет подсчитывать энергетическую ценность и сбалансированность завтрака. Итак: В ячейку F1 введите Энергетическая ценность завтрака. Завершить ввод можно 2 способами: нажать Enter или перейти в следующую ячейку (см. УПРАЖНЕНИЕ 1)

УПРАЖНЕНИЕ 4. Ввод текстовых строк и постоянных числовых значений в таблицу

Введите Наименование блюда в ячейку А2.

Введите Белки, на 100 г в ячейку В2.

Введите Жиры, на 100 г в ячейку С2.

Введите Углеводы, на 100 г в ячейку D2.

Введите Ккал, на 100 г в ячейку Е2.

Введите Вес, г в ячейку F2.

Введите Белки, г в ячейку G2.

Введите Жиры, г в Н2

Введите Углеводы, г в ячейку I2.

Введите Энергетическая ценность, ккал в ячейку J2.

Отлично! Теперь Вы готовы к вводу строк в столбце А, в которых будут указаны названия готовых блюд, входящих в рацион, в столбце В указано количество ккал в 100 г продукта, в столбцах C, D, и Е указаны содержание белков, жиров и углеводов в 100 г продукта. Введите в таблицу соответствующие значения строк и столбцов.

Энергетическая ценность завтрака

А B C D E

| | |

2. | Наименование продукта | Белки, на 100 г | Жиры, на100 г | Углеводы, на 100 г | Ккал, на100 г

3. | Завтрак | | | |

4. | Хлеб ржаной | 5 | 1 | 42,5 | 204

5. | Хлеб пшеничный | 6,7 | 0,7 | 50,3 | 240

6. | Капуста тушеная | 2,1 | 5,2 | 10,4 | 100

7. | Каша гречневая | 4,75 | 4,7 | 28,6 | 183

8. | Каша овсяная | 3,85 | 5,65 | 19,7 | 149

9. | Сосиска | 10,3 | 17,9 | 0,4 | 200

10. | Макароны с сыром | 4,3 | 5,4 | 25 | 170

11. | Омлет | 7,7 | 10,1 | 2,5 | 135

12. | Масло слив | 0,4 | 78,5 | 0,5 | 734

13. | Сдоба | 6,4 | 7,2 | 45,8 | 273

14. | Сахар | 0 | 0 | 95,5 | 390

15. | Конфеты «Ассорти» | 3,6 | 35,6 | 53,1 | 563

Форматирование ЭТ

Вы видите, что текстовые данные, которые выходят за пределы ячеек и переходят в ячейки соседних столбцов справа, обрезаются при вводе данных в эти соседние ячейки. Метод решения этой проблемы заключается в расширении столбцов, в которых содержатся такие данные.

УПРАЖНЕНИЕ 5. Форматирование заголовка таблицы

Щелкните в ячейке F1, чтобы сделать ее текущей.

Задайте полужирный шрифт, размер 12

Удерживая нажатой клавишу Shift, щелкните в ячейке J1, чтобы выделить диапазон ячеек F1:J1.

Щелкните по кнопке Объединить и поместить в центре. (Данная кнопка находится на панели инструментов форматирования).

Если вы все сделали правильно, заголовок таблицы разместится по центру между столбцами F и J.

УПРАЖНЕНИЕ 6. Регулировка ширины столбца с помощью команды Автоподбор ширины.

Выделите диапазон заполненных ячеек в столбце A.

Выберите команду: Формат4Столбец4Автоподбор ширины.

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

УПРАЖНЕНИЕ 7. Переименование листа рабочей книги

Щелкните правой кнопкой по ярлычку текущего листа (Лист№)

В появившемся контекстном меню левой кнопкой щелкните Переименовать

Введите с клавиатуры Завтрак. Нажмите Enter

ПОВТОРИТЕ УПРАЖНЕНИЯ 3 -7 ДЛЯ 2, 3, 4 ЛИСТОВ РАБОЧЕЙ КНИГИ ДЛЯ РАСЧЕТА ЭНЕРГЕТИЧЕСКОЙ ЦЕННОСТИ ОБЕДА, ПОЛДНИКА И УЖИНА

Энергетическая ценность обеда

А B C D E

2. | Наименование продукта | Белки,

на 100 г | Жиры,

на 100 г | Углеводы, на 100 г | Ккал, на100 г

3. | Обед | | |

4. | Хлеб ржаной | 5 | 1 | 42,5 | 204

5. | Хлеб пшеничный | 6,7 | 0,7 | 50,3 | 240

6. | Бульон мясной | 0,14 | 0 | 0,76 | 4

7. | Суп рисовый | 0,64 | 0,07 | 6,4 | 30

8. | Борщ | 0,7 | 3 | 4,1 | 47

9. | Суп молочный | 2,61 | 2,97 | 10,8 | 82

10. | Котлеты | 17,4 | 8,07 | 9,85 | 186

11. | Говядина тушеная | 25,4 | 8,9 | 1,4 | 192

12. | Курица отварная | 18,4 | 7,9 | 1,6 | 154

13. | Картофельное пюре | 2 | 3,4 | 14,6 | 99

14. | Капуста тушеная | 2,1 | 5,2 | 10,4 | 100

15. | Винегрет | 1,2 | 4,7 | 13,1 | 102

16. | Салат из свеклы | 0,96 | 4,7 | 12,1 | 97

17. | Салат из капусты | 1,34 | 4,23 | 10,1 | 86

18. | Компот | 0,06 | 0 | 17,4 | 71

19. | Масло слив | 0,4 | 78,5 | 0,5 | 734

20. | Сдоба | 6,4 | 7,2 | 45,8 | 273

21. | Сахар | 0 | 0 | 95,5 | 390

22. | Конфеты «Ассорти» | 3,6 | 35,6 | 53,1 | 563

Энергетическая ценность полдника

А B C D E

2. | Наименование продукта | Белки, на 100 г | Жиры, на 100 г | Углеводы, на 100 г | Ккал, на100 г

3. | Полдник | |

4. | Хлеб ржаной | 5 | 1 | 42,5 | 204

5. | Хлеб пшеничный | 6,7 | 0,7 | 50,3 | 240

6. | Печенье | 10,8 | 8,5 | 66,4 | 395

7. | Молоко | 2,8 | 3,5 | 4,5 | 62

8. | Масло слив | 0,4 | 78,5 | 0,5 | 734

9. | Сдоба | 6,4 | 7,2 | 45,8 | 273

10. | Сахар | 0 | 0 | 95,5 | 390

11. | Конфеты «Ассорти» | 3,6 | 35,6 | 53,1 | 563

Энергетическая ценность ужина

А B C D E

2. | Наименование продукта | Белки, на100 г | Жиры, на100 г | Углеводы, на 100 г | Ккал, на100 г

3. | Ужин | | |

4. | Хлеб ржаной | 5 | 1 | 42,5 | 204

5. | Хлеб пшеничный | 6,7 | 0,7 | 50,3 | 240

6. | Печенье | 10,8 | 8,5 | 66,4 | 395

7. | Курица отварная | 18,4 | 7,9 | 1,6 | 154

8. | Рыба жареная | 14 | 6,04 | 4,9 | 132

9. | Блинчики с творогом | 10,2 | 11,5 | 34,5 | 285

10. | Картофельное пюре | 2 | 3,4 | 14,6 | 99

11. | Капуста тушеная | 2,1 | 5,2 | 10,4 | 100

12. | Винегрет | 1,2 | 4,7 | 13,1 | 102

13. | Салат из свеклы | 0,96 | 4,7 | 12,1 | 97

14. | Салат из капусты | 1,34 | 4,23 | 10,1 | 86

15. | Каша гречневая | 4,75 | 4,7 | 28,6 | 183

16. | Каша овсяная | 3,85 | 5,65 | 19,7 | 149

17. | Макароны с сыром | 4,3 | 5,4 | 25 | 170

18. | Вареники ленивые | 11,2 | 11,8 | 21,1 | 238

19. | Омлет | 7,7 | 10,1 | 2,5 | 135

20. | Масло слив | 0,4 | 78,5 | 0,5 | 734

21. | Сдоба | 6,4 | 7,2 | 45,8 | 273

22. | Сахар | 0 | 0 | 95,5 | 390

23. | Конфеты «Ассорти» | 3,6 | 35,6 | 53,1 | 563

УПРАЖНЕНИЕ 8. Ввод данных (переменных значений) в таблицу.

Как вы знаете, суточный калораж для студентов 17 — 18 лет должен составлять, примерно,»2600 ккал. Питание должно:

ü покрывать суточные энергозатраты,

ü быть полноценным (в организм должно поступать достаточное количество белков, жиров и углеводов) Для студентов 17 — 18 лет суточный рацион должен содержать белков»90 г, жиров»90 г, углеводов 360 г

ü быть сбалансированным (соотношение белков, жиров и углеводов – 1: 1: 4 или по-другому белков должно быть»16,5%, жиров»16,5% и углеводов»67%), разнообразным.

ü На завтрак взрослый человек должен потребить»20 — 25 % суточного рациона или»625 ккал, обед должен быть»40 – 45 % или 1000 — 1125 ккал, полдник -»5 — 10 % или» 125 — 250 ккал, ужин»20 — 25 % или»625 ккал.

Предлагаем пример суточного рациона питания для студента 17 — 18 лет. В столбце F, в соответствующих ячейках введите вес в граммах завтрака.

Завтрак

¨ Хлеб ржаной 50 г

¨ Хлеб пшеничный 50 г

¨ Каша гречневая 150 г

¨ Сосиска 80 г

¨ Сахар 10 г | Полдник

¨ Печенье 20 г

¨ Молоко 100 г

Обед

¨ Хлеб ржаной 100 г

¨ Хлеб пшеничный 50 г

¨ Борщ 200 г

¨ Говядина тушеная 100 г

¨ Капуста тушеная 250 г

¨ Компот 200 г | Ужин

¨ Хлеб ржаной 100 г

¨ Рыба жареная 150 г

¨ Картофельное пюре 200 г

¨ Салат из свеклы 100 г

¨ Конфеты «Ассорти» 10 г

УПРАЖНЕНИЕ 9. Ввод формул подсчета; белков, жиров, углеводов и энергетической ценности (калоража).

Необходимо рассчитать, сколько ккал содержит 50 г ржаного хлеба.

Для этого составляем пропорцию

100 г | — | 204 ккал

50 г | — | Х ккал

Х=(50*204)/100=102

Следовательно, в 50 г ржаного хлеба содержится 102 ккал. Для того, чтобы не высчитывать вручную калораж каждого блюда заменим конкретные значения веса и ккал на 100 г на адреса соответствующих ячеек. Тогда наша формула будет иметь вид:

Х=(F4*E4)/100

Все формулы в электронных таблицах начинаются со знака =. Поэтому в ячейку J4 введите =(F4*E4)/100

Аналогично составляются формулы для подсчета белков, жиров и углеводов. Введите:

— в ячейку G4 формулу =(F4*В4)/100

— в ячейку Н4 формулу =(F4*С4)/100

— в ячейку I4 формулу =(F4*D4)/100

УПРАЖНЕНИЕ 10. Копирование формул с помощью маркера заполнения.

Чтобы закончить создание формул подсчета белков, жиров, углеводов и калоража в нашей таблице, используем маркер заполнения для копирования формул из ячеек G4, H4, I4 и J4 в диапазон ячеек G4:J15.

Выделите диапазон ячеек G4:J4. Для этого подведите указатель мыши в ячейку G4 (он имеет форму белого большого крестика), прижмите левую кнопку мыши и, не отпуская ее, протяните мышь до ячейки J4. Диапазон ячеек G4:J4 выделится единой черной рамкой табличного курсора.

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

Перетаскивайте его вниз до тех пор, пока рамка табличного курсора (имеющая светло-серый цвет) не охватит ячейки с G4 по J15 включительно. Когда вы отпустите кнопку мыши,Excel скопирует формулу из ячеек G4, Н4, I4, J4 во все ячейки столбцов G, H, I, J до 15-тых.

УПРАЖНЕНИЕ 11. Суммирование значений с помощью функции Автосумма

Для того чтобы убедится в том, как легко пользоваться Автосуммой, вы создадите одну формулу. Она будет находиться в ячейке G3 и подсчитывать суммарное количество белков завтрака в диапазоне ячеек G4:G15.

Щелкните на ячейке G3, чтобы сделать ее активной.

Щелкните на кнопке å, на панели инструментов. Excel вводит =СУММ().

Курсор находится в скобках. Введите G4:G15

Завершите ввод (Enter). В ячейке G3 отобразится результат.

Скопируйте, с помощью маркера заполнения формулу из ячейки G3 в диапазон ячеек G3:J3, для подсчета жиров, углеводов и энергетической ценности завтрака.

Завершите ввод (Enter).

УПРАЖНЕНИЕ 12. Графическое представление сбалансированности рациона с помощью мастера диаграмм.

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

Выделите диапазон ячеек G2:I3

Щелкните по кнопке Мастер диаграмм

В появившемся окне выберите тип диаграммы Круговая

Щелкните по кнопке Далее

Мастер диаграмм в текстовом поле «Диапазон» укажет выделенные вами ячейки, переключатель «Ряды в» установите в «строках»

Щелкните по кнопке Далее

На вкладке Заголовки в текстовом поле «Название диаграммы» введите «Сбалансированность завтрака»

На вкладке Подписи данных переключатель Подписи значений установите в позицию Доля

Щелкните по кнопке Далее

Поместим диаграмму на имеющемся листе

Щелкните по кнопке Готово

УПРАЖНЕНИЕ 13. Использование закрепленных областей в качестве заголовков рабочего листа

В этом упражнении вы примените команду Закрепить область для «замораживания» на экране заголовков строк и столбцов. Это называется созданием заголовков рабочего листа

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

В строке меню щелкните Окно4Закрепить область. Excel отобразит тонкие черные пересекающиеся линии. Эти линии указывают границы областей, которые вы только что «заморозили» на экране

Щелкайте по кнопке прокрутки со стрелкой вправо на горизонтальной полосе прокрутки до тех пор, пока на экране не останутся столбцы F, G, H, I, J и диаграмма.

ПОВТОРИТЕ УПРАЖНЕНИЯ 8 — 13 ДЛЯ ЛИСТОВ ОБЕД, ПОЛДНИК И УЖИН РАБОЧЕЙ КНИГИ ДЛЯ СОЗДАНИЯ ФОРМУЛ, ДИАГРАММ И ЗАГОЛОВКОВ РАБОЧЕГО ЛИСТА

УПРАЖНЕНИЕ 14. Создание итогового листа

Переименуйте последний лист рабочей книги Суточный рацион

На этом листе в ячейку А1 введите Энергетическая ценность суточного рациона

Введите Белки, г в ячейку А2.

Введите Жиры, г в В2

Введите Углеводы, г в ячейку С2.

Введите Энергетическая ценность, ккал в ячейку D2.

УПРАЖНЕНИЕ 15 Суммирование значений из различных листов рабочей книги

В ячейку А3 введите =

Щелкните ярлычок листа Завтрак и щелкните в ячейке G3 (белки, г завтрака)

Введите +

Щелкните ярлычок листа Обед и щелкните в ячейке G3 (белки, г обеда)

Введите +

Щелкните ярлычок листа Полдник и щелкните в ячейке G3 (белки, г полдника)

Введите +

Щелкните ярлычок листа Ужин и щелкните в ячейке G3 (белки, г ужина)

Нажмите Enter

Скопируйте с помощью маркера заполнения формулу из ячейки А3 в диапазон А3:D3

С ПОМОЩЬЮ МАСТЕРА ДИАГРАММ СОЗДАЙТЕ ДИАГРАММУ СБАЛАНСИРОВАННОСТИ СУТОЧНОГО РАЦИОНА

УПРАЖНЕНИЕ 16. Сохранение рабочей книги

Щелкните по Файл.

Щелкните по Сохранить.

Откройте свою папку: (Папка преподавателя на сетевом диске®Папка группы).

В поле Имя файла задайте имя.

Щелкните по кнопке Сохранить или нажмите Enter.

УПРАЖНЕНИЕ 17. Изучение работы таблицы. Подбор рациона питания для различных групп труда

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

УПРАЖНЕНИЕ 18. Сохранение рабочей книги под другим именем.

Щелкните по Файл.

Щелкните по Сохранить как.

Откройте свою папку (Папка преподавателя на сетевом диске ® Папка группы).

В поле Имя файла задайте имя

Щелкните по кнопке Сохранить или нажмите Enter.

УПРАЖНЕНИЕ 19. Выход из Excel

Выполните выход любым известным вам способом.

Вопросы для самоподготовки и контроля:

1. Основные термины и понятия ЭТ

2. Запуск Excel

3. Способы перемещения табличного курсора

4. Ввод данных в ячейку

5. Функция Автосумма

6. Копирование с помощью маркера заполнения

7. Автоподбор ширины

8. Форматирование заголовка таблицы

9. Создание диаграмм

10. Вставка листов в книгу

11. Переименование рабочего листа

12. Закрепление областей

13. Суммирование значений из различных рабочих листов

14. Сохранение и сохранение под другим именем рабочей книги

15. Выход из Excel