Создание электронной таблицы и заполнение формулами, обрамление.

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

Вычисления в электронных таблицах

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

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

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

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

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

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

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1.В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой — как относительный.

Использование стандартных функций

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

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

Использование мастера функций.При выборе пункта Другие функции запускается Мастер функций, облегчающий выбор нужной функции. В списке Категория выбирается категория, к которой относится функция (если определить категорию затруднительно, используют пункт Полный алфавитный перечень), а в списке Функция —конкретная функция данной категории. После щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками.

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

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

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

Задание 1.

1. Создайте таблицу следующего образца.

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

Суммарн. расход = Зарплата + Премия + Закуп, материалов +

+ Амортиз. отчисл. + Накладные расходы

Прибыль= Доход от продажи - Суммарн. расход.

Задание 2.

1. Составьте электронную таблицу следующего вида:

Количество выпавших осадков.

Образец выполнения задания

Задание 3.

Составьте электронную таблицу, вычисляющую 1-й член, и сумму арифметической прогрессии по формулам

a_n=a₁ + d(n-1) S_n = (a₁ + a_n)*n/2,

где a — первый член прогрессии, a d — разность арифметической прогрессии.

1. В ячейку А1 и введите заголовок таблицы «Вычисление, n-го члена и суммы арифметической прогрессии». Заголовок будет размещен в одну строчку и займет несколько ячеек правее А1.

2. В ячейку A3 введите «d», в ячейку В3 — «n», в С3 — «a_n» в D3 — «S_n». Для набора нижних индексов воспользуйтесь командой ФОРМАТ → ЯЧЕЙКИ, выберите вкладку Шрифт и включите нижний индекс. Выровняйте по центру и примените полужирный шрифт.

3. В ячейку А4 введите величину разности арифметической прогрессии (в нашем примере это 0,725) и заполните 9 нижних ячеек таким же числом.

4. Следующий столбик заполните последовательностью чисел от 1 до 10.

5. Введите в ячейку С4 значение первого члена арифметической прогрессии (-2).

6. Выделите ячейку С5 и наберите в ней формулу =С4+А4, для этого, набрав знак равенства, щелкните мышью по ячейке С4, и в строке формул появится ее адрес, затем наберите знак + и щелкните по ячейке А4, зафиксируйте ее нажатием Enter, в ячейке окажется результат вычисления по формуле, а в Строке формул сама формула.

7. Заполните формулой, «протащив» маркер заполнения вниз, ряд ячеек, ниже С5. Ссылки в формуле изменились относительно смещения формулы.

8. Аналогично введите в ячейку D4 формулу =(-2+С4)*B4/2 для подсчета суммы п первых членов арифметической прогрессии и заполните формулами нижние ячейки, протащив вниз маркер заполнения.

9. Выделите все ячейки таблицы, содержащие данные без заголовка «Вычисление n -го члена и суммы арифметической прогрессии», и выполните команду ФОРМАТ → СТОЛБЕЦ → Подгонка ШИРИНЫ.

10. Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки.

11. Выделите четыре ячейки от А1 до D1 и выполните команду ФОРМАТ → ЯЧЕЙКИ → ВЫРАВНИВАНИЕ и установите в положение «Центрировать по выделению» (Горизонтальное выравнивание) и «Переносить по словам». Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

12. Выделите таблицу (без заголовка) и выполните команду ФОРМАТ → ЯЧЕЙКИ → ГРАНИЦА, определите толстую линию и границу вокруг ячеек.

13. Выделите блок ячеек, относящихся к заголовку: от Al доD2, и, проделав те же операции, установите Контур. В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой.

Образец выполнения задания

Контрольные вопросы

1. Как происходит вставка стандартных функций? Опишите работу с Мастером функций.
2. С помощью каких команд происходит автоподбор ширины столбца?

Лабораторная работа № 3