Тема: электронные таблицы Excel: работа с рабочими книгами. Создание и редактирование форму и диаграмм.

Цель: научиться открывать и сохранять файлы рабочих книг; выполнять расчетные операции с использованием мастера функций; строить и редактировать диаграммы.

Теоретические сведения

Применение ЭТ упрощает работу с данными и позволяет получать результаты без проведения расчетов вручную или специального программирования. Наиболее широкое применение ЭТ нашли в экономических и бухгалтерских расчетах, но и в научно-технических задачах ЭТ можно использовать эффективно, например для:

· проведения однотипных расчетов над большими наборами данных;

· автоматизации итоговых вычислений;

· решения задач путем подбора значений параметров, табулирования формул;

· обработки результатов экспериментов;

· проведения поиска оптимальных значений параметров;

· подготовки табличных документов;

· построения диаграмм и графиков по имеющимся данным.

 

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

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

При запуске программы Excel перед Вами появится рабочий лист. Рабочее поле представляет собой таблицу, столбцы которой озаглавлены буквами, строки - цифрами. Если у вас столбцы обозначаются номерами вместо букв, то причина в том, что у вас в качестве параметра Стиль ссылок выбран стиль R1C1, а на А1. Чтобы изменить стиль ссылок на А1, выберите команду Параметры в меню Сервис и щелкните на вкладке Общие. Снимите флажок, установленный напротив параметра Стиль ссылок R1C1, и щелкните на кнопке ОК.

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

 

Задание

1. Откройте новый файл и введите необходимые данные.

2. Автоматически просуммируйте значения строк и столбцов.

3. Просуммируйте строки и столбцы одновременно.

4. Вычислите прибыль за март месяц.

5. Введите в ячейку текущую дату.

6. Определите средние затраты на товары.

7. Присвойте блоку ячеек С11:С20 имя Март_2008.

8. Найдите максимальное и минимальное значения.

9. Переместите данные между ячейками разных рабочих листов.

10. Определить значение прихода с учетом ежемесячного роста объема продаж (1,50%).

11. Скопируйте формулу и измените относительные адреса на абсолютные.

12. Примените автоформат для оформления вида таблицы.

13. Добавьте условное форматирование.

14. Удалите условное форматирование.

15. Измените стандартные числовые форматы.

16. Создайте диаграмму для указанного диапазона ячеек.

17. Измените исходные данные диаграммы.

18. Измените порядок рядов данных.

19. Завершите работу с программой.

 

Технология работы

1. Продолжим составление торгового бюджета. Для этого необходимо ввести численные данные в соответствующие ячейки:

	В	С	D	E	F	G	H	I
		Март	Апрель	Май	Июнь	Июль	Август	Всего
	Приход Затраты на товары Полная выручка Статьи расходов Реклама Аренда помещений Налоги Проценты Расходы всего Прибыль

 

2. Посчитайте автоматически сумму строк и столбцов:

2.1. Перейдите на лист Бюджет 2002.

2.2. Выделите ячейку I11. Теперь с помощью Автосуммы вычислим сумму С11:Н11.

2.3. На панели инструментов Стандартные щелкните по S. Блок ячеек С11:Р11 на рабочем листе становится окруженным бегущей границей, а в строке формул появляется =СУММ(С11:Н11).

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

2.4. Нажмите Enter или еще раз по S. В ячейке I11 будет помещен результат вычислений.

2.5. Таким же образом поместите в I12 сумму чисел в строке Затраты на товары.

2.6. Щелкните на I13 и нажмите S. Программа предлагает просуммировать столбец, а не строку =СУММ(I11:I12).

2.7. Чтобы просуммировать в строке Полная выручка, щелкните на С13, нажмите Shift + Н13 + Enter. Теперь в I13 значение требуемой суммы.

3. Подсчитайте сумму строк и столбцов одновременно.

3.1. Выделите диапазон ячеек С15:I19.

3.2. Щелкните по S. Результаты суммирования по строках появятся в ячейках I15:I19, а по столбцам в ячейках C19:I19.

4. Вычислите прибыль за март месяц.

4.1. В ячейке С20 введите =С13–С19.

4.4. Нажмите Enter и формула будет введена. В ячейке С20 появится значение прибыли за март.

4.5. Скопируйте формулу ячейки С20 в ячейки D20:I20.

5. Использование некоторых функций не требует указания адресов ячеек или какой-либо другой информации. Для ввода в ячейку текущей даты:

5.1. Выделите ячейку В4.

5.2. Наберите =сегодня() и нажмите Enter.

6. Для определения средних затрат на товары:

6.1. В ячейке К10 наберите Среднее.

6.2. Выделите К12, затем на стандартной панели инструментов щелкните на кнопке Вставка функции. Откроется диалоговое окно Мастера функций.

6.3. В списке Категория выделите Статистические и выберите функцию СРЗНАЧ.

6.4. В поле Число1 введите С12:Н12.

6.6. Щелкните по ОК. В ячейке К12 находится среднее значение затрат на товары за март - 19755,83.

7. Имена блоков ячеек используют в формулах для большей наглядности, делают их более понятными. Например, сразу понятно, что вычисляет формула МАКС (Затраты_на_товары), в отличии от формулы МАКС(С12:Н12). Для присвоения блоку ячеек имени:

7.1. Выделите ячейки С11:С20.

7.2. В строке формул щелкните в Поле имени и наберите Март_2002, нажмите Enter. Выделенный диапазон ячеек теперь называется Март_2002.

7.3. Аналогично присвойте диапазону ячеек С12:Н12 имя Затраты_на_товары.

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

8.1. Выделите ячейку L10 и наберите Макс.

8.2. В ячейке L12 определите максимальное значение с использованием в мастере формул команды меню Вставка Имя-Вставить (Затраты_на_товары).

8.8. Результат 20201 появится в ячейке L12.

8.9. Выделите ячейку М10 и наберите Мин.

8.10. Аналогично определите минимальное значение затрат на товары.

9. Научимся составлять формулы с относительными и абсолютными адресами.

Для определения значения прихода с учетом ежемесячного роста объема продаж (1,50%):

10.1. Выделите ячейку С7 и после числа 1,50 введите %.

10.2. Выделите ячейку Е11, наберите =D11+(D11*$С$7) и нажмите клавишу Enter.
В ячейке Е11 появилось точное значение прихода с учетом ежемесячного роста объема продаж.

10.3. Скопируйте формулу ячейки Е11 в диапазон ячеек F11:I11. Результат формулы во всех ячейках будет зависеть от содержимого ячейки $С$7.

11. Для изменения относительного адреса на абсолютный:

11.1. Выделите ячейку I19. В этой ячейке находится формула с относительными адресами.

11.2. Скопируйте ячейку I19 в ячейку I20. Теперь в ячейку I20 суммирование происходит в диапазоне D20:I20, в котором нет данных, и в результате получается 0.

11.3. В меню Правка выберите команду Отменить. Это может быть команда Отменить вставку, или Отменить перетаскивание, или Отменить автозаполнение - в зависимости от того, каким способом копирования вы пользовались.

11.4. Выделите ячейку I19.

11.5. В строке формул выделите D19:I19. Быстро выделить отдельные адреса можно, дважды щелкнув на них.

11.6. Нажмите клавишу F4, а затем клавишу Enter. Теперь относительные адреса заменены на абсолютные.

11.7. Скопируйте ячейку I19 в ячейку I20. Формула скопирована в новую ячейку и в ячейке I20 находится число 33361, так как относительные адреса были предварительно заменены на абсолютные.

11.8. Нажмите клавишу Del.

12. В этом и следующих упражнениях Вы выделите диапазоны ячеек и примените к ним различные варианты форматов с помощью команды Автоформат.

12.1. На листе Бюджет 2008 выделите ячейки С6:D8.

12.2. В меню Формат выберите команду Автоформат. Откроется диалоговое окно Автоформат.

12.3. В окне Список форматов выделите строку Цветной 2 и щелкните на кнопке ОК. Теперь данные выделенного блока оформлены в формате Цветной 2.

13. Для добавления условного форматирования:

13.1. На рабочем листе Бюджет 2008 выделите блок ячеек D20:I20.

13.2. В меню Формат выберите команду Условное форматирование. Откроется диалоговое окно Условное форматирование.

13.3. В группе Условие 1 в первом поле должно быть выбрано Значение. Во втором поле выберите Больше или равно.

13.4. В третьем поле наберите 8500 и щелкните на кнопке Формат. Откроется окно Формат ячеек.

13.5. Щелкните на стрелке в поле Цвет и в открывшемся меню выберите красный цвет.

13.6. В группе Начертание выберите тип Полужирный.

13.7. Щелкните на кнопке ОК и затем щелкните на кнопке ОК в окне Условное форматирование.

13.8. Содержимое тех ячеек из строки Прибыль, значение которых превышает 8500, выделено красным полужирным шрифтом. При необходимости измените ширину ячеек.

14. Для удаления условного форматирования:

14.1. Выделите блок ячеек D20:I20.

14.2. В меню Формат выберите команду Условное форматирование.

14.3. В окне Условное форматирование щелкните не кнопке Формат.

14.4. В окне Формат ячеек щелкните на кнопке Очистить.

14.5. Щелкните на кнопке ОК и затем щелкните на кнопке ОК в окне Условное форматирование.

14.6. Условное форматирование выделенных ячеек теперь удалено.

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

15.1. Сделайте текущим лист Отчет.

15.2. Выделите ячейки С5:Е13.

15.3. Выберите команду Ячейки в меню Формат. Откроется диалоговое окно Формат ячеек.

15.4. Выберите вкладку Число. На этой вкладке находятся различные виды числовых форматов.

15.5. В списке Числовые форматы выделите строку (все форматы), затем в окне списка Тип выделите введенный вами формат ###0 тыс.р.

15.6. Щелкните на кнопке ОК.

15.7. Если ширины столбцов недостаточно, и вместо чисел в некоторых ячейках видны символы #, то выделите столбцы С,D, Е и щелкните дважды на границе заголовков столбцов. Ширина выделенных столбцов изменится в соответствии с длиной текста в каждом из них.

16. Создайте диаграмму

16.1. Используя мастер построения диаграмм, постройте ее для диапазона ячеек B10:H13 и B19:H19.

16.2. Последовательно выполняя шаги, выберите вид «гистрограмма», подпишите оси и расположите диаграмму на имеющемся листе