Ввод формулы. Вычисления по формулам
Основные понятия MS Excel. Типы данных
Основными понятиями в MS Excel 2007 являются: таблица, столбец, строка, ячейка. Горизонтальная линейка формирует адрес столбца - А, В, С,., и т.д., а левая вертикальная линейка формирует адрес строки - 1, 2, 3... и т.д. Всего в таблице может быть 16 384 столбца и 1 048 576 строк. На пересечении строки и столбца расположена ячейка. Адрес ячейки формируется из адреса столбца и адреса строки - ВЗ, D1 и т.д. Ячейки в таблицах можно объединять для записи заголовков, в ячейках можно записывать формулы, которые полностью видны в строке формул, где их можно при необходимости менять (рис. 2.1).
Рис. 2.1
Типы данных, которые могут быть обработаны в электронном процессоре MS EXCEL, самые разные (рис. 2.2).
Рис. 2.2
Форматирование таблицы
Запустите редактор электронных таблиц MS Excel 2007 (при стандартной установке MS Office выполнить Пуск • Программы • Microsoft Excel и создать новую электронную книгу (Кнопка Office • Создать).
Практическая работа
Цель работы: Научиться создавать, форматировать и заполнять таблицы.
· Задание 1. Создайте документ по предложенному образцу (таблица 2.1) и отформатируйте таблицу.
Порядок выполнения задания
1. Откройте Лист 1, нажав левой клавишей мыши на его ярлык внизу окна программы.
2. Удерживая левую клавишу мыши, выделите таблицу из трех столбцов и шести строк. Для обозначение границ выделенной области нажмите вкладку Главная • Шрифт • Гриницы • Все.
3. Заполните таблицу по образцу табл. 2.1.
4. Выполните заливку трех столбцов разными цветами. Для этого выделите ячейки с результатами расчетов и нажмите правую кнопку мыши. В контекстном меню выберите Формат ячеек • Заливка • Цвет фона.
5. Отформатируйте заголовок таблицы. Выделите интервал ячеек от А1 до С1 и нажмите правую клавишу мыши. В контекстном меню выберите Формат ячеек • Выравнивание. В открывшемся окне установите следующие параметры ячейки: выравнивание по горизонтали и по вертикали - по центру, отображение - объединение ячеек.
6. Выделите интервал ячеек от А2 до С2 и нажмите правую клавишу мыши. В контекстном меню выберите Формат ячеек • Выравнивание • По горизонтали / по вертикале / по центру.
7. Выделите интервал ячеек от A3 до С6 и выберите вкладку Главная • Выравнивание • Выровнять текст по левому краю. 
Таблица 2.1
| Мировые океаны | ||
| Название | Площадь, тыс. км2 | Наибольшая глубина, м |
| Тихий | ||
| Атлантический | ||
| Индийский | ||
| Северный Ледовитый |
• Задание 2. Создайте документ по предложенному образцу (таблица 2.2) и отформатируйте таблицу.
1. Наэтом же Листе 1 наберите табл. 2.2 и 2.3.
2. В первой строке объедините ячейки и отформатируйте текст по центру, во второй строке заголовки расположите также по центру.
3. Выполните выравнивание слева в первом и во втором столбцах. Выберите вкладку Главная • Выравнивание • Выровнять текст по левому краю 
4. Выполните выравнивание по центру в третьем и четвертом столбцах. Выделите столбцы и нажмите правую клавишу мыши. В контекстном меню выберите Формат ячеек • Выравнивание • По горизонтали/по вертикали/по центру.
Таблица 2.2
| Самые большие озера мира | |||
| Название | Географическое положение | Площадь, тыс. км2 | Наибольшая глубина, м |
| Эри | Сев. Америка | ||
| Чудское с Псковским | Европа | ||
| Чад | Африка | ||
| Хубсугул | Азия | ||
| Титикака | Южн. Америка | ||
| Танганьика | Африка | ||
| Таймыр | Азия | ||
| Онтарио | Сев. Америка | ||
| Онежское | Европа | ||
| Ньяса | Африка | ||
| Мичиган | Сев. Америка | ||
| Маракайбо | Южн. Америка | ||
| Ладожское | Европа | ||
| Каспийское море | Европа | ||
| Иссык-Куль | Азия | ||
| Гурон | Сев. Америка | ||
| Виннипег | Сев. Америка | ||
| Виктория | Африка | ||
| Верхнее | Сев. Америка | ||
| Венерн | Европа | ||
| Ван | Азия | ||
| Бол. Невольничье | Сев. Америка | ||
| Бол. Медвежье | Сев. Америка | ||
| Балхаш | Азия | ||
| Байкал | Азия |
Таблица 2.3
| Самые полноводные реки мира | |||
| Название | Географическое положение | Длина, км | Площадь бассейна, км2 |
| Янцы | Азия | ||
| Юкон | Сев. Америка | ||
| Хуанхэ | Азия | ||
| Токанитис | Южн. Америка | ||
| Сан-Франсиску | Южн. Америка | ||
| Ориноко | Южн. Америка | ||
| Обь (с Иртышом) | Азия | ||
| Нил (с Кагерой) | Африка | ||
| Миссисипи | Сев. Америка | ||
| Меконг | Азия | ||
| Лена | Азия | ||
| Ла Плата (с Параной) | Южн. Америка | ||
| Конго | Африка | ||
| Дунай | Европа | ||
| Волга | Европа | ||
| Амур (с Аргунью) | Азия | ||
| Амазонка (с Укаяли) | Южн. Америка | ||
| Амазонка | Южн. Америка |
Таблица 2.3.1
| МАТЕРИКИ И ИХ РАЗМЕРЫ | |||
| Материк | Площадь суши, млн км2 | Площадь шельфа, Тыс. км2 | Длина береговой линии (без островов), тыс. км |
| Евразия | 53,4 | 100,0 | |
| Африка | 30,3 | 30,5 | |
| Северная Америка | 24,2 | 60,0 | |
| Южная Америка | 18,3 | 26,0 | |
| Австралия | 7,6 | 19,7 | |
| Антарктида | 14,0 | 30,0 |
5. Переименуйте Лист I в «География» и сохраните файл под именем «Книга 1» в личной папке.
Автозаполнение таблиц
Практическая работа
Цель работы: Научиться применять автозаполнение в таблице.
• Задание. С помощью функции Автозаполнение создайте календарь на любой год, например, на 2015 г. (таблица 2.4) и вставьте рисунки, соответствующие всем временам года (рис. 2.3).
•
Порядок выполнения задания
1. Откройте файл «Книга 1»и на Листе 2 создайте таблицу с границами из 25 строк и 23 столбцов.
2. Объедините ячейки верхней строки и с выравниванием по центру впишите заголовок.
3. Впишите в левый столбец третьей строки день недели «Понедельник». Выделите ячейку и потяните за правый нижний угол «+» до 10-й строки. Ячейки заполняются днями недели.
4. Скопируйте ячейки с днями недели и вставьте в соответствующие ячейки первого столбца.
5. Объедините по шесть столбцов в 2, 10, 18-й строке, куда впишите название соответствующего месяца.
6. Впишите числа месяца в строках Понедельники Вторник,выделите эти две ячейки, тем самым задав шаг изменения чисел, и потяните мышкой за правый нижний угол (при этом курсор должен обязательно принять форму знака «+») до ячейки, соответствующей строке Воскресенье.Аналогично повторите для каждой недели и для каждого месяца.
7. Для вставки рисунка выберите меню Вставка • Иллюстрации • Клип.Выберите в разделе просматривать Выделенные коллекциии нажмите кнопку Упорядочить клипы.Выберите в меню раздел Коллекции Microsoft Office • рисунок.Нажмите на стрелку, расположенную на рисунке, и скопируйте его. Поставьте курсор мышки в выбранное место, нажмите на правую кнопку мыши и выберите в контекстном меню команду Вставить.Рисунок разместится в выбранном вами месте.
Рис. 2.3
Таблица 2.4
| КАЛЕНДАРЬ НА 2015 ГОД | |||||||||||||||||||||
| Январь | Февраль | Март | Апрель | ||||||||||||||||||
| Понедельник | |||||||||||||||||||||
| Вторник | |||||||||||||||||||||
| Среда | |||||||||||||||||||||
| Четверг | |||||||||||||||||||||
| Пятница | |||||||||||||||||||||
| Суббота | |||||||||||||||||||||
| Воскресенье | |||||||||||||||||||||
| Май | Июнь | Июль | Август | ||||||||||||||||||
| Понедельник | |||||||||||||||||||||
| Вторник | |||||||||||||||||||||
| Среда | |||||||||||||||||||||
| Четверг | |||||||||||||||||||||
| Пятница | |||||||||||||||||||||
| Суббота | |||||||||||||||||||||
| Воскресенье | |||||||||||||||||||||
| Сентябрь | Октябрь | Ноябрь | Декабрь | ||||||||||||||||||
| Понедельник | |||||||||||||||||||||
| Вторник | |||||||||||||||||||||
| Среда | |||||||||||||||||||||
| Четверг | |||||||||||||||||||||
| Пятница | |||||||||||||||||||||
| Суббота | |||||||||||||||||||||
| Воскресенье |
8. Переименуйте Лист 2 в «Календарь».
9. Сохранить файл под именем «Книга 1» в личной папке.
Построение диаграмм
Практическая работа
Цель работы: Создать таблицу «Продуктовая корзина детского сада» по образцу. Построить по введенным данным круговую диаграмму, отформатировать ее по предложенному образцу.
• Задание 1. На Листе 3 в Книге 1 создать таблицу «Продуктовая корзина детского сада» по образцу табл. 2.5.
Таблица 2.5
| Продуктовая корзина детского сада | |
| Продукт | Количество, кг |
| Мясо | |
| Картофель | |
| Капуста | |
| Морковь | |
| Фрукты |
• Задание 2. Построить круговую диаграмму (рис. 2.4) по результатам расчетов с использованием Мастера диаграмм. Произвести форматирование диаграммы: изменить учет секторов, заголовок диаграммы; для области диаграммы применить текстурную заливку.
Порядок выполнения задания
1. Выделите в таблице интервал ячеек А1:В7 с данными расчета результатов и выберите вкладку Вставка • Диаграммы • Круговая • Объемная разрезанная круговая.
2. Для внесения заголовка диаграммы и подписей данных выберите всплывающуювкладку Работа с диаграммами • Макет • Подписи • Подписи данных • Авто.
3. Чтобы применить текстурную заливку, выделите правой кнопкой мыши область диаграммы и выберите в контекстном меню Формат области построения.В открывшемся окне выберите закладку Заливка • Рисунок или текстура.В данном разделе выберите параметры текстурной заливки диаграммы.
Рис. 2.4
• Задание 3.Построить гистограмму (рис. 2.5), измените заголовок диаграммы; для области диаграммы применить градиентную заливку.
Порядок выполнения задания
1. Постройте гистограмму по результатам расчетов табл. 2.6 с использованием Мастера диаграмм.
2. Для этого выделите интервал ячеек А1:В7 с данными расчета результатов и выберите вкладку Вставка • Диаграммы • Гистограмма • Объемная с накоплением.
3. Введите название диаграммы при помощи всплывающей вкладки Работа с диаграммами • Макет • Подписи • Подписи данных.
4. Присвойте Листу 3 имя «Продуктовая корзина».
5. Сохраните Лист 3 в уже созданном файле «Книга 1».
Рис. 2.5
• Задание 4. Самостоятельно построить диаграммы при помощи Мастера диаграмм и подписать в них заголовки и данные.
1. Вфайле Книга 1 создайте новый лист и назовите его «География».
2. Постройте следующие диаграммы по рис. 2.6-2.9 аналогично Заданиям 3-4 данной практической работы.
Рис. 2.6
Рис. 2.7
Рис. 2.8
Рис. 2.9
3. Сохранить файл под именем «Книга 1» в личной папке.
Ввод формулы. Вычисления по формулам
| Основные правила создания формул 1. Формула всегда начинается со знака «=» (равно). Она полностью отражается в строке формул. 2. Аргументами формул обычно являются ссылки на ячейки (адреса ячеек). 3. Адреса ячеек бывают относительными (адрес В1 - вводится в формулу вписыванием или щелчком левой мыши по ячейке) и абсолютными (адрес D2 вводится аналогично. Чтобы он стал абсолютным, после указания ячейки необходимо нажать клавишу F4. Перед именем столбца и строки появится знак «$» - $D$2). При копировании формулы в столбце или строке относительные адреса меняются, а абсолютные остаются неизменными. 4. Формулы можно вводить с использованием клавиатуры и мыши или создавать с использованием функций. |
Рассмотрим на примерах использование относительных и абсолютных адресов ячеек.
Создайте произвольную таблицу из трех столбцов: В, С, D. Ячейки В и С заполните произвольными значениями. Требуется вычислить результат в столбце D, начиная с ячейки D1. Для этого в ячейку D1 установите курсор (рис. 2.10, а) и напишите, например, формулу =В1*С1, нажмите клавишу Enter. В ячейку D1 будет внесен результат умножения (рис. 2.10, б).
Выделитеячейку D1 (рис. 2.10, в) и потяните за нижний правый угол (+) до конца столбца. Все ячейки столбца заполнятся соответствующими результатами, изменение относительно адресов отражено в строке формул (рис 2.10, г).
Рис. 2.10
Создайте другую таблицу из трех столбцов: А - фамилия покупателя, В – количество товара, С - стоимость. Цена единицы товара внесена ячейку D2 (рис. 2.11,а) и является величиной, постоянной для данной таблицы, т.е. адрес этой ячейки в формуле постоянен, он называется абсолютным и обозначается $D$2 (для создания абсолютного адреса нажмите F4, перед именем столбца и строки появится знак «$»).
Вычислите в столбце С стоимость, на которую был куплен товар каждым покупателем, начиная сячейки С1. Формулу для вычисления =B1*$D$2 напишите в ячейку (рис. 2.11, б) нажмите клавишу Filler. В ячейке появится результат умножения С1 (рис. 2.11.,в).Выделите ячейку С1 (рис. 2.11,г) и потяните за нижний правый угол(«+») до конца столбца. Все ячейки столбца заполняются результатами (рис. 2.11,д), изменение относительных адресов и постоянство абсолютных адресов отразится в строке формул.
Рис. 2.11
Практическая работа
Цель работы: Ввести таблицу, установить границу, выполнить заливку, объединение ячеек, произвести требуемое выравнивание текстов в ячейках, вычислить необходимые данные по формулам.
Порядок выполнения работы
1. Откройте файл «Книга 1». На Лист 5 введите таблицу по образцу (см. табл. 2.6). Выберитеграницу и заливку произвольно.
2. Установите курсор в ячейку А1 и объедините пять ячеексправа. Введите заголовок Таблицы «СтартМастер» и выполнитевыравнивание но центру.
3. Для указания курса рубля и доллара сформируйте вторуюстроку по аналогии с табл. 2.6.
4. Для форматированияназваний столбца A таблицы выделите третьюстроку (нажатием на номере сроки). Нажмите правуюкнопку мыши и выберите вконтекстном меню Формат ячеек.В открывшимся окне выберите вкладкуВыравниваниеи задайте параметры форматирования ячеек: выравнивание по вертикали и по горизонтали – по центру, отображение - переносить по словам.
5. Обозначьте границы выделенной области А4:Е25при помощи вкладки Главная • Шрифт • Границы • Все.
6. Заполните первые 3 столбца по образцу табл. 2.6.
7. Произведите форматирование значений в столбцах В (Розница) и С (Опт). Для этого выделите блок данных отячейки В4 до ячейки С25, нажмите правую кнопку мыши и выберите в контекстном меню Формат ячеек.В открывшемся окне выберите вкладку Числои задайте параметры форматирования ячеек: числовой формат - денежный, обозначение - нет, число десятичных знаков после запятой - 2.
8. Произведите расчеты в столбцах D (Розн., р) и Е (Опт., р.) по формуле. Для этого в ячейку D4 установите курсор и впишите формулу =В4*28,5, нажмите клавишу Enter. В ячейку D4 будет внесен результат умножения. Аналогично в ячейку Е4 впишите формулу =С4*28,5. В этом способе было использовано числовое выражение курса доллара.
Можно провести данные расчеты с использованием вместо числового выражения курса доллара абсолютного адреса ячейки, в данном случае Е2. Для этого укажите в формуле ее адрес - $Е$2. Формула будет выглядеть следующим образом: для ячейки D4=В4*$Е$2, для ячейки Е4=С4*$Е$2.
Произвести расчет в остальных ячейках можно при помощи функции автозаполнения.
9. Чтобы рассчитать результат в строке «Итого» необходимо выделить каждый столбец без заголовка и выбрать вкладку Главная • Редактирование • СуммаΣ.
10. Выделите ячейки с результатами расчетов и выполните заливку любым цветом. Для этого нажмите правую кнопку мыши, в контекстном меню выберите Формат ячеек • Заливка • Цвет фона.
11.Сохраните файл в Книге 1.
Таблица 2.6