Как составить вычислительную таблицу

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

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

В Excel формулы начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.

Excel поддерживает следующие операторы:

• Арифметические операции:
  • сложение (+);
  • умножение (*);
  • нахождение процента (%);
  • вычитание (-);
  • деление (/);
  •  экспонента (^).
• Операторы сравнения:
  • = равно;
  • < меньше;
  • > больше;
  • = больше или равно;
  • не равно.
• Операторы связи:
  • : диапазон;
  • ; объединение;
  • & оператор соединения текстов.

Формула	Составляющие элементы
=27+36 =А1+А2-АЗ =45%*АЗ ,	Цифры Ссылки на ячейки Цифры и ссылки на ячейки
=СУММ(А1:А5), =МАКС(АЗ:А5)	Предварительно определенные функции
=(А1+А2)/АЗ	Ссылки на ячейки при заданном порядке действий

Таблица 22. Примеры формул

Упражнение

Вставка формулы -25-А1+АЗ

Предварительно введите любые числа в ячейки А1 и A3.

1. Выберите необходимую ячейку, например В1.
2. Начните ввод формулы со знака=.
3. Введите число 25, затем оператор (знак -).
4. Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
5. Введите следующий оператор(знак +).
6. Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
7. Завершите ввод формулы нажатием клавиши Enter. В ячейке В1 получите результат.

Автосуммирование

Кнопка Автосумма (AutoSum) — ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.

1. Выберите ячейку, в которую надо поместить результат суммирования.
2. Щелкните кнопку Автосумма — ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.

Функция «Автосумма» автоматически трансформируется в случае добавления и удаления ячеек внутри области.

Упражнение

Создание таблицы и расчет по формулам

1. Введите числовые данные в ячейки, как показано в табл. 23.

	А	В	С	D	Б	F
	Распределение сотрудников по образованию
		Магнолия	Лилия	Фиалка	Всего
	Высшее	25	20	9
	Среднее спец.	28	23	21
	ПТУ	27	58	20
в	Другое	8	10	9
	Всего
	Без высшего

Таблица 23. Исходная таблица данных

1. Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
2. Щелкните кнопку Автосумма — ∑ или нажмите Alt+=.
3. Повторите действия пунктов 2 и 3 для ячеек С7 и D7.

Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).

1. Выберите ячейку В8 и наберите знак (=).
2. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
3. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
4. Нажмите Enter (в ячейке В8 будет вычислен результат).
5. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
6. Сохраните файл с именем Образование_сотрудников.х1s.

Таблица 24. Результат расчета

	А	B	С	D	Е	F
	Распределение сотрудников по образованию
		Магнолия	Лилия	Фиалка	Всего
	Высшее	25	20	9
	Среднее спец.	28	23	21
	ПТУ	27	58	20
	Другое	8	10	9
	Всего	88	111	59
	Без высшего	63	91	50

Тиражирование формул при помощи маркера заполнения

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

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

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

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

Упражнение

Тиражирование формул

1.Откройте файл Образование_сотрудников.х1s.

1. Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
2. Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
3. Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.

	А	В	С	D	Е	F
	Распределение сотрудников по образованию
		Магнолия	Лилия	Фиалка	Всего
	Высшее	25	20	9	=СУММ{ВЗ:03)
	Среднее спец.	28	23	21	=СУММ(В4:04)
	ПТУ	27	58	20	=СУММ(В5:05)
	Другое	8	10	9	=СУММ(В6:06)
	Всего	88	111	58	=СУММ(В7:07)
	Без высшего	63	91	49	=СУММ(В8:08)

Таблица 25. Изменение адресов ячеек при тиражировании формул

Относительные и абсолютные ссылки

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

Использование относительных ссылок аналогично указанию направления движения по улице — «идти три квартала на север, затем два квартала на запад». Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.

Обычно ссылки на ячейки описываются и используются как относительные (формат записи А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок.

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

Абсолютная ссылка на ячейку .иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: «Идите на пересечение Арбата и Бульварного кольца». Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара — $.

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 — так называемые смешанные ссылки).

Упражнение

Создание абсолютной ссылки

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

	A	B	C
	Часовая ставка	100р.
	Ф. И. 0 .	Часов	Зарплата
	Иванов	40
	Петров	30
	Сидоров	25

Таблица 26. Расчет зарплаты

1. В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.

При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;

1. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
2. Скопируйте формулу в ячейки С4 и С5.
3. Сохраните файл (табл. 27) под именем Зарплата.xls.

	A	B	C
1	Часовая ставка	100р.
	ФИО	Часов	Зарплата
3	Иванов	40	4000р.
4	Петров	30	3000р.
5	Сидоров	25	2500р.

Таблица 27. Итоги расчета зарплаты

Имена в формулах

Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен:

• имена могут содержать не более 255 символов;
• имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
• имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
• имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.

В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.

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

Создание имени

1. Выделите ячейку В1 (табл. 26).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define).
3. Введите имя Часовая ставка и нажмите ОК.
4. Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка.

Создание нескольких имен

1. Выделите ячейки ВЗ:С5 (табл. 27).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create), появится диалоговое окно Создать имена (рис. 88).
3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК.
4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.

Рис. 88. Диалоговое окно Создать имена

Можно в формулу вставить имя вместо абсолютной ссылки.

1. В строке формул установите курсор в то место, где будет добавлено имя.
2. Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.

1. Выберите нужное имя из списка и нажмите ОК.

Ошибки в формулах

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

Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.

Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка #Н/Д! является сокращением термина «неопределенные данные». Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка #ССЫЛКА! появляется, когда в формуле используется недопустимая ссылка на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено содержимое других ячеек.

Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.

Кроме перечисленных ошибок, при вводе формул может появиться циклическая ссылка.

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

Рис. 89. Циклическая ссылка

Индикатор циклической ссылки в строке состояния показывает ««ссылку на последнюю зависимую формулу.

Когда формула, содержащая циклическую ссылку, находится на рабочем листе, появится сообщение, указанное на рис. 89.

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

Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).

Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.

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

Рис. 90. Мастер функций

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

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

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

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.

Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.

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

Упражнение

Вычисление величины среднего значения для каждой строки в файле Образование.хls.

1. Выделите ячейку F3 и нажмите на кнопку мастера функций.
2. В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ, нажмите на кнопку Далее.
3. Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
4. Нажмите ОК.
5. Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).

Рис. 91. Ввод аргумента в мастере функций

Таблица 28. Таблица результатов расчета с помощью мастера функций

	А	В	С	D	Е	F
	Распределение сотрудников по образованию
		Магнолия	Лилия	Фиалка	Всего	Среднее
	Высшее	25	20	9	54	18
	Среднее спец.	28	23	21	72	24
	ПТУ	27	58	20	105	35
в	Другое	8	10	9	27	9
	Всего	88	111	59	258	129

Для ввода диапазона ячеек в окно мастера функций можно мышью обвести на рабочем листе таблицы этот диапазон (в примере B3:D3). Если окно мастера функций закрывает нужные ячейки, можно передвинуть окно диалога. После выделения диапазона ячеек (B3:D3) вокруг него появится бегущая пунктирная рамка, а в поле аргумента автоматически появится адрес выделенного диапазона ячеек.

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

Построение вычислительных таблиц

Копирование данных листа стандартными средствами Excel.

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

Как добавить ячейки в таблицу Excel.

Вставка и удаление ячеек в таблицы на листе. Добавление диапазонов в таблицу. Горячие клавиши для вставки пустых ячеек.

Как вставить строку или столбец в Excel между строками и столбцами.

Вставка и удаление строк и колонок листа. Горячие клавиши для быстрого добавления и удаления строки и колонки. Редактирование таблиц прайс-листов.

Как переместить и скопировать лист Excel в другую книгу.

Управление книгами при копировании или перемещении листов между ними. Пример как переместить или скопировать лист в другой файл.

Перемещение и расстановка листов в документе Excel.

Копирование, перемещение и создание нескольких листов одновременно. Полный контроль над листами рабочей книги.

Создание, копирование и перемещение листов в книгах Excel.

Управление листами рабочих книг. Добавление, перемещение и копирование листов вместе с их содержимым.

Редактирование строк и столбцов в таблицах.

Практические примеры по редактированию и группировки данных в таблицах с добавлением, удалением, скрытием строк и столбцлов.

Создание и управление таблицами в Excel.

Работа с таблицами данных, от простого создания до сложных корректировок таблиц.

1 2

Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.

Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.

Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.

Как работать в Excel с таблицами. Пошаговая инструкция

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

• Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
• Первая строка таблицы должна содержать короткие, уникальные заголовки;
• Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
• Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
• В таблице не должно быть пустых строк и абсолютно пустых столбцов.

1. Выделите область ячеек для создания таблицы

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

2. Нажмите кнопку “Таблица” на панели быстрого доступа

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

• Отображение строки заголовков – включает и отключает заголовки в таблице;
• Строку итогов – включает и отключает строку с суммой значений в колонках;
• Чередующиеся строки – подсвечивает цветом чередующиеся строки;
• Первый столбец – выделяет “жирным” текст в первом столбце с данными;
• Последний столбец – выделяет “жирным” текст в последнем столбце;
• Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
• Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

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

• Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

• Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

• “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
• “Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
• “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
• В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.

Как посчитать сумму в таблице Excel

Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

• Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

• Выберите пункт “Закрепить верхнюю строку”:

• Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

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

• Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

• Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

• В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

• Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Вопросы занятия:

·                   вычислительные таблицы;

·                   соответствие классов объектов;

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

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

Бывают таблицы двух типов, «объекты-свойства» или сокращённо ОС, а также «объекты-объекты-один» или сокращённо ООО.

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

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

Рассмотрим пример.

Результаты своих вычислений ребята могут представить в виде таблицы

Обратим внимание на то, что данная таблица описывает разные свойства нескольких объектов, поэтому она относится к типу «объекты-свойства» и что числа в столбце «Площадь» получены путём перемножения чисел в соответствующих строках столбцов «Длина» и «Ширина». Последняя строка данной таблицы называется итоговой, она содержит только общую площадь бумаги, так как вычисление общей длины и ширины не задано по условию задачи. Общая площадь бумаги получена путём сложения площадей отдельных листов.

Рассмотрим таблицу расхода кормов на ферме, за первое полугодие две тысячи пятнадцатого года

Мы можем определить, что данная таблица относится к типу «объекты-объекты-один», так как она описывает всего одно свойство — «Расход кормов», у пар объектов классов «месяц» и «животные». Значения в столбце всего за период, получены путём сложения расхода кормов на коров и на свиней. В ячейках итоговой строки содержаться суммы данных соответствующих столбцов. Так мы видим, что за указанный период на коров ушло десять тысяч триста сорок килограмм корма, а на свиней – двадцать тысяч триста шестьдесят килограмм. Обратим внимание, что общий расход кормов за полугодие, можно получить двумя путями. Рассчитав сумму чисел в последнем столбце, или же сложив расходы кормов за полугодье на свиней и на коров. Для проверки можно рассчитать это число обоими способами и сравнить результаты.

Данный тип таблиц удобен для ведения различных учётов.

Ещё одним важным применением таблиц является решение логических задач. Важное свойство классов объектов, которое можно при этом использовать: взаимно-однозначное соответствие.

Чтобы классы объектов находились во взаимно-однозначном соответствии, должны выполняться три условия:

— В рассматриваемых классах должно быть одинаковое количество объектов;

— каждый объект первого класса должен быть связан рассматриваемым свойством лишь с одним объектом второго класса;

— и наоборот, каждый объект второго класса должен быть связан рассматриваемым свойством лишь с одним объектом первого класса.

Следовательно, в составленной таблице типа ООО, в каждой строке и каждом столбце будет лишь одна единица, обозначающая связь между двумя объектами

Для примера решим одну задачу с помощью таблицы:

Маша, Таня, Петя и Коля недавно праздновали дни рождения. Им исполнилось 9,10,11 и 12 лет. Им подарили: мяч, машинку, куклу и самокат. Определить кому из ребят что подарили и сколько лет им исполнилось, если среди ребят нет ровесников, и каждому из них досталась по одному подарку.

А также известно, что:

·                   Петя и Коля не получили мяч;

·                   Маша получила куклу;

·                   Коля получил не машинку;

·                   самокат достался кому-то на одиннадцатилетие;

·                   мяч достался десятилетней девочке;

·                   Пете не девять лет.

Обратим внимание, что в данной задаче мы рассматриваем объекты классов «Ребята», «Подарки» и «Возраст», причём между данными классами существует взаимно-однозначное соответствие, так как каждому из ребят досталось по одному подарку и среди ребят нет ровесников.

Так мы можем построить две таблицы типа ООО для классов «Ребята» и «Подарки», а также для классов «Ребята» и «Возраст», но для удобства мы объединим их и построим одну таблицу следующего вида.

Условимся, что если между объектами существует связь, в соответствующей клетке мы будем ставить знак плюс, а при её отсутствии – минус, удобно в начале, рассмотреть связи между классами «Ребята» и «Подарки», они будут рассматриваться на основании утверждений:

— Петя и Коля не получили мяч

— Маша получила куклу

— Коля получил не машинку

На основании первого утверждения мы можем поставить знак минус, в строке мяч, под именами мальчиков. На основании второго утверждения, мы можем полностью заполнить строку кукла, и столбец Маша, в части подарки. Теперь, если мы посмотрим на строку, мяч, мы увидим, что его могла получить только Таня, теперь мы можем заполнить её столбец в части подарки. На основании третьего утверждения, мы можем сказать, что, Коля мог получить только самокат, соответственно Пете досталась машинка.

Теперь перейдём к части возраст. Здесь у нас есть три утверждения:

— самокат достался кому-то на одиннадцатилетние;

— мяч достался десятилетней девочке

— Пете не девять лет.

Из первого утверждения, так как самокат достался коле, можно сказать, что ему исполнилось одиннадцать лет. Так же из второго утверждения можно сказать, что Тане исполнилось десять лет.

Из третьего утверждения, следует что Пете может быть только двенадцать лет, следовательно, маше –девять.

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

Рассмотрим задачу.

На фабрике по производству воздушных шаров заинтересовались объёмами продукции. Всего на фабрике 3 цеха. По каждому цеху известны объёмы продукции за первое и за второе полугодье 2015 года. Нас попросили найти увеличение объёмов продукции во втором полугодье, по сравнению с первым полугодьем по каждому цеху, а также по всей фабрике, а результаты представить в виде вычислительной таблицы.

Откроем файл «фабрика.docx» из нашей рабочей папки. Для начала продумаем структуру таблицы. В таблице будут представлены значения объёмов производства по трём цехам и по всей фабрике, а также нам нужно две строки для заголовков. Следовательно, в таблице будет шесть строк. Нам нужно представить значения объёмов продукции за два полугодья, значения увеличения объёма продукции, а также нам нужен один столбец для заголовков, следовательно, в таблице должно быть четыре столбца. Создадим таблицу с указанными параметрами.

Для этого на вкладке вставка, щёлкнем по иконке таблица и выберем размер таблицы четыре на шесть. Затем в первой строке объединим последние три ячейки. Для этого выделим их, установив курсор в первую из них, а затем, удерживая нажатой левую кнопку мыши, переместим курсор в последнюю из них. Теперь щёлкнем по любой из них правой кнопкой мыши и в появившемся контекстном меню выберем команду объединить ячейки.

Заполним строку и столбец заголовка. В первом столбце начиная, с третей строки мы введём наименования цехов, а в последней его ячейке запишем слово «Всего:». В первой строке заголовка мы запишем «Объем продукции в тысячах штук». Во второй строке заголовка, начиная со второй ячейки, запишем «первое полугодье», «второе полугодье», «увеличение во втором полугодье»

Внесём в таблицу информацию из условия задачи. Далее нам необходимо ввести формулы для расчёта изменения объёмов продукции. При этом важно знать, что столбцы таблицы обозначаются буквами английского алфавита от A до Z, а строки – числами, начиная с единицы, таким образом, чтобы найти увеличение объёма производства для первого цеха, нам нужно из содержимой ячейки C3 вычесть содержимое ячейки B3. Ведём эту формулу в соответствующую ячейку, для этого установим в неё курсор и на вкладке «Макет», в группе «Данные» вы берём пункт «Формула». Все формулы начинаются со знака равенства, следовательно, в появившемся диалоговом окне, в строке формула мы должны ввести «=С3-B3» и нажать кнопку «Ок». Обратим внимание что теперь в соответствующей ячейке находится результат вычисления по введённой формуле. Так же запишем формулы и для следующих двух ячеек в этом столбце, для второго цеха формула будет «=С4-B4», а для третьего «=С5-B5».

Теперь нам необходимо найти объёмы производства для всего завода, для этого установим курсор в соответствующую строку, в столбец для первого полугодья. Чтобы найти объем производства завода в первом полугодье, мы должны просуммировать объёмы производств всех трёх цехов. Для этого не нужно записывать длинную формулу, достаточно использовать стандартную функцию вычисления суммы чисел «=sum» параметром которой, в круглых скобках, будет указано слово «аbove», которое указывает что необходимо складывать числа, в ячейках этого же столбца над ней. Так же для этой функции можно перечислить нужные ячейки через запятую, или указать слова «Right» — когда необходимо просуммировать ячейки справа, «Left» — когда необходимо просуммировать ячейки слева, и «Below» — когда нужно просуммировать ячейки под указанной. Эту же формулу зададим и для следующих двух ячеек.

Так мы получили таблицу объёмов выпускаемой продукции для фабрики по производству воздушных шаров. Сохраним её. Для этого в меню «файл» выберем пункт «сохранить».

На этом уроке мы рассмотрели вычислительные таблицы. Мы узнали, что вычислительные таблицы – это таблицы, в которых одни свойства объектов вычисляются из других.

Классы объектов находятся во взаимно-однозначном соответствии, если:

в рассматриваемых классах одинаковое количество объектов;

каждый объект первого класса связан рассматриваемым свойством лишь с одним объектом второго класса;

каждый объект второго класса связан рассматриваемым свойством лишь с одним объектом первого класса.

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

Задание 1

С помощью таблицы решим следующую задачу.

На школьном дворе разбивают 5 клумб треугольной формы. Первая клумба представляет собой равнобедренный треугольник с длинами сторон 5, 5 и 7 метров. Вторая клумба имеет форму прямоугольного треугольника, длины её сторон — 3, 4 и 5 метров. Длины сторон третьей клумбы равны 4, 3 и 3 метра. Четвёртая клумба представляет собой равносторонний треугольник, длина стороны которого равна 4 метрам. Длины сторон пятой клумбы равны 7, 5 и 7 метров.

Сколько провода нужно для обозначения границ каждой из этих клумб?

Хватит ли 50 м провода, чтобы обозначить на земле границы всех клумб?

1. В текстовом процессоре постройте таблицу:

2. Занесите в таблицу данные (длины сторон клумб) из условия задачи.

3. Ответ на первый вопрос можно получить, вычислив Значение последнего столбца таблицы: периметр треугольника — сумма длин трёх его сторон. Для этого:

1) установите курсор в ячейку, предназначенную для периметра первой клумбы;

 2) в разделе Работа с таблицами на вкладке Макет в группе Данные щёлкните на кнопке Формула;

3) числа, подлежащие суммированию, находятся левее ячейки, в которой должен быть помещен периметр; в диалоговом окне вам будет предложена формула =SUM(LEFT); если эта формула верна, щёлкните на кнопке ОК;

4) перейдите в следующую ячейку и повторите действия пункта 3; если будет предложена неподходящая формула — измените её, выбрав подходящую в диалоговом окне;

5) аналогичным образом вычислите периметр остальных треугольников.

4. Для ответа на второй вопрос просуммируйте периметры всех треугольников. Для этого:

1) установите курсор в правую нижнюю ячейку таблицы;

 2) для нахождения суммы чисел, находящихся над ячейкой с курсором, используйте формулу =SUM(ABOVE).

 2) используйте инструмент Сумма.

5. Под таблицей запишите ответ на вопрос, поставленный в задаче.

6. Сохраните документ в личной папке

Домашнее задание