Какие типы формул используются в электронных таблицах.

Формулы

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

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

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

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

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

Рис. 5.3. Диалоговое окно в развернутом и свернутом виде

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

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

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

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

Копирование содержимого ячеек

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

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

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

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

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

Автоматизация ввода

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

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

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

Автозаполнение числами. При работе с числами используется метод автозаполнения. В правом нижнем углу рамки текущей ячейки имеется черный квадратик - маркер заполнения. При наведении на него указатель мыши (он обычно имеет вид толстого белого креста) приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном или вертикальном направлении.

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

Пусть, например, ячейка А1 содержит число 1, Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши иперетащите маркер заполнения так, чтобы рамка охватила ячейки А1, В1 и С1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка Заполнить Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке OK программа Excel автоматически заполняет ячейки в соответствии с 1 заданными правилами.

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

Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В, Введем в ячейку С1 формулу =А1+В1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.

В
таблице 5.1 приведены правила обновления ссылок при автозаполнении вдольстроки или вдоль столбца.

Таблица 5.1. Правила обновления ссылок при автозаполнении

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

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

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

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

А
ргументы функции. Как только имя функции выбрано, на экране появляется диалоговое окно Аргументы функции (в предыдущих версиях Ехсе l это окно рассматривалось как палитра формул). Это окно, в частности, содержит значение, которое получится, если немедленно закончить ввод формулы (рис. 5.4).

Рис. 5.4. Строка формул и диалоговое окно Аргументы функции

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

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

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

Началось всё с того, что нужно было настроить работу одной из бухгалтерских систем отчётности. Это я сделал, а затем меня спросили, знаю ли я Excel. Экселя я особо не знал, но подумал, что по ходу дела разберусь, поэтому согласился помочь. А сделать нужно было таблицу для распечатки корешков по зарплате:)

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

Поэтому, задумавшись над таким положением вещей, я и решил написать статью, прочитав которую, можно было бы вникнуть в основы работы с электронными таблицами. В статье все примеры будут показаны на базе бесплатного компонента OpenOffice Calc, но они применимы и для Microsoft Office Excel.

Рабочие книги и их структура

Рабочими книгами в сфере электронных таблиц называют файлы, в которых эти таблицы хранятся. Для пакета Microsoft Office стандартными форматами файлов Excel будут XLS или XLSX , а для OpenOffice Calc - ODS .

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

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

На одном рабочем листе Вы можете создать фактически неограниченное количество таблиц с различными расчётами. Однако, на практике чаще всего один лист содержит всего одну таблицу.

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

Панель меню . Может быть выполнена в виде классической панели с раскрывающимися списками функций, а может реализовываться в виде вкладок (например, ленточный интерфейс Microsoft Office Excel 2007). Содержит доступ ко всем возможностям и настройкам программы.
Панель форматирования . Обычно отдельная панель или вкладка, на которой находятся инструменты форматирования текста и внешнего вида ячеек.
Навигационный список . Обычно находится в левом верхнем углу над рабочим листом и отображает адреса текущих выделенных ячеек. Также может быть использован для быстрого перехода к ячейке с заданным адресом (вводите адрес и жмёте Enter).
Поле ввода формул . Специальное поле, в котором можно задать как простое содержимое выделенной ячейки, так и специальную формулу для вычисления этого содержимого.
Строка состояния . Отображает дополнительную полезную информацию о типе выбранной ячейки, её текущем значении и другие служебные данные.

Ячейки электронных таблиц

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

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

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

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

Самая главная закавыка с ячейками кроется в первой вкладке окна "Формат ячеек" (в OpenOffice Calc она называется "Числа", а в Microsoft Office Excel - "Число"). Дело в том, что здесь задаётся тип данных ячейки и во многих готовых таблицах он не всегда стандартный. Если у Вас, например, введённое число превращается в дату или нормально не отображается текст, то проблема как раз в этих параметрах.

Также советую обратить внимание на кнопки на панели инструментов, которые позволяют увеличивать/уменьшать разрядность чисел в ячейках или включать денежный формат. Эти кнопки автоматически меняют тип данных (не нужно лезть в меню) для выделенных ячеек, выводя знаки после запятой или название валюты по умолчанию (задаётся в языковых параметрах Панели управления компьютера):

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

Формулы

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

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

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

Поскольку у нас всего три ячейки с цифрами, сумму которых нам нужно получить, то мы можем воспользоваться элементарными арифметическими действиями. Просто перечисляем по порядку адреса всех ячеек через знак "плюс". Для завершения ввода нажимаем Enter или кнопку с зелёной галочкой слева от поля ввода формулы.

Отдельно стоит сказать об адресации ячеек. Каждая ячейка имеет адрес вида: "буква столбца""цифра строки". Однако, это только один из видов ссылок - относительный.

Относительные ссылки могут автоматически меняться при изменении количества строк или столбцов. Например, в ячейке A2 у нас сейчас имеется формула: "=A1+B1+C1". Теперь, если мы вставим новую строку над первой, все наши ячейки опустятся вниз, но сохранят свои значения, а в формуле (которая теперь будет в ячейке A3) номер строки автоматически сменится на второй: "=A2+B2+C2":

Если же Вам нужно точно привязать формулу к конкретной ячейке, чтобы её значение не менялось, то Вам следует использовать абсолютные ссылки. Абсолютный адрес ячейки отличается только тем, что перед каждой её координатой Вы добавляете значок "доллара", например: $A$1. Если же значок "$" добавлять только к одной из координат, то мы получим смешанную ссылку, "привязанную" к номеру строки или столбца.

Ссылка на ячейку другого листа той же рабочей книги:

Calc: =ИмяЛиста.АдресЯчейки (например: =Лист2.A1);
Excel: =ИмяЛиста!АдресЯчейки (например: =Лист2!A1).

Ссылка на ячейку на листе другой открытой рабочей книги:

Calc: -;
Excel: =[ИмяКниги]ИмяЛиста!АдресЯчейки (например: =[Книга2]Лист1!A1).

Ссылка на ячейку на листе другой закрытой в данный момент рабочей книги:

Calc: ="file:///ПутьКФайлу/ИмяФайла"#$ИмяЛиста.АдресЯчейки (например: ="file:///H:/source.ods"#$Лист1.A1);
Excel: ="ПолныйПутьКФайлу\[ИмяКниги(файла)]ИмяЛиста"!АдресЯчейки (например: ="D:\Отчёты\[Книга1.xls]Лист1"!A1).

Calc не имеет отдельного вида формул для получения ссылок на ячейки в другом, открытом в данный момент, файле. Вместо этого Вам нужно использовать третий вариант с полным видом ссылки. При этом, обратите внимание на слеши в путях. В Excel, как в Проводнике Windows, используются обратные слеши, тогда как в Calc используются абсолютные ссылки в стиле UNIX-подобных систем!

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

Принцип диапазона в том, что мы указываем адреса первой и последней смежных ячеек через двоеточие. Табличный же процессор автоматически получает значения всех ячеек, которые находятся между заданными точками и производит над ними указанные нами действия (в нашем случае суммирование).

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

Наиболее полный же список соответствий Вы можете найти на официальном WIKI-ресурсе OpenOffice .

Стилизация и распечатка таблиц

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

Во всех ячейках числа будут вводиться вручную (или браться из других файлов с ведомостями), а в трёх будут автоматически рассчитываться при помощи элементарных формул и арифметических действий: СУММ или SUM для ячеек "Всего насчитано" и "Всего удержано", а также "Всего насчитано"-"Всего удержано".

После ввода всех полей наша табличка будет иметь примерно следующий вид:

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

Также желательно отцентрировать текст в ячейках. Выделите их, а затем вызовите из контекстного меню пункт "Формат ячеек". В открывшемся окошке перейдите на вкладку "Выравнивание" и задайте нужные параметры центровки и переноса слов:

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

Теперь, когда разметка у нас готова, отрегулируем ширину ячеек так, чтобы они все уместились на одной странице. Теперь осталось немного. Нам нужно объединить несколько ячеек в левом верхнем углу для записи в них имени получателя. Для этого выделим четыре ячейки, вызовем их контекстное меню и выберем пункт "Объединить ячейки" (для Excel) или меню "Формат" - "Объединить ячейки" (для Calc).

Последний штрих - добавление рамок для нашей таблицы. Снова выделяем все используемые ячейки и в контекстном меню выбираем пункт "Формат ячеек". Переходим на вкладку "Обрамление" (Calc) или "Граница" (Excel) и настраиваем внешний вид рамок (для каждого элемента границы можно задать свой стиль):

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

Выводы

В нашей статье мы рассмотрели только самые базовые действия с электронными таблицами. На практике у Вас может возникнуть множество вопросов. В Excel уже встроена хорошая справочная система, в которой можно найти большинство ответов. Для Calc же этим целям соответствует русскоязычный WIKI-портал .

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

P.S. Разрешается свободно копировать и цитировать данную статью при условии указания открытой активной ссылки на источник и сохранения авторства Руслана Тертышного.

Основные типы и форматы данных

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

Числа . Для представления чисел могут использоваться несколько различных форматов (числовой, экспоненциальный, дробный и процентный ). Существуют специальные форматы для хранения дат (например, 25.09.2003) и времени (например, 13:30:55), а также финансовый и денежный форматы (например, 1500,00р.), которые используются при проведении бухгалтерских расчетов.

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

Экспоненциальный формат применяется, если число, содержащее большое количество разрядов, не умещается в ячейке. В этом случае разряды числа представляются с помощью положительных или отрицательных степеней числа 10. Например, числа 2000000 и 0,000002, представленные в экспоненциальном формате как 2 × 10 6 и 2 × 10 -6 , будут записаны в ячейке электронных таблиц в виде 2,00Е+06 и 2,00Е-06.

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

Текст . Текстом в электронных таблицах является последовательность символов, состоящая из букв, цифр и пробелов. Например, последовательность цифр "2004" - это текст. По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).

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

Например, формула =А1+В1 обеспечивает сложение чисел, хранящихся в ячейках А1 и В1, а формула =А1*5 - умножение числа, хранящегося в ячейке А1, на 5. При изменении исходных значений, входящих в формулу, результат пересчитывается немедленно.

В процессе ввода формулы она отображается как в самой ячейке, так и в строке формул (рис. 1.1). После окончания ввода, которое обеспечивается нажатием клавиши {Enter}, в ячейке отображается не сама формула, а результат вычислений по этой формуле.

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

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

Ввод в формулы имен ячеек можно осуществлять выделением нужной ячейки с помощью мыши.

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

Для быстрого копирования данных из одной ячейки сразу во все ячейки определенного диапазона используется специальный метод: сначала выделяется ячейка и требуемый диапазон, а затем вводится команда [Заполнитъ-вниз ] (вправо, вверх, влево ).

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

1. Какие типы данных могут обрабатываться в электронных таблицах?

2. В каких форматах данные могут быть представлены в электронных таблицах?

1. Задание с кратким ответом. Запишите формулы:

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

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

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

Так, при копировании формулы из активной ячейки С1, содержащей относительные ссылки на ячейки А1 и В1, в ячейку D2 имена столбцов и номера строк в формуле изменятся на один шаг соответственно вправо и вниз. При копировании формулы в ячейку ЕЗ имена столбцов и номера строк в формуле изменятся на два шага соответственно вправо и вниз и т. д. (табл. 1.3).

Таблица 1.3. Относительные ссылки

	А	В	С	D	Е
1			=A1*B1
2				=B2*C2
3					=C3*D3

Создадим в электронных таблицах фрагмент таблицы умножения. В столбцах А и В разместим числа от 1 до 9, а в столбце С - их произведения.

Для этого введем в ячейки А1 и В1 число 1, в ячейку С1 - формулу =А1*В1, а в ячейки А2 и В2 - формулы =А1+1 и =В1+1 с относительными ссылками. Тогда для заполнения таблицы достаточно будет просто скопировать формулы в нижележащие ячейки (табл. 1.4).

Таблица 1.4. Фрагмент таблицы умножения

	А	В	С
1	1	1	=A1*B1
2	=А1+1	=В1+1	=А2*В2
3	=А2+1	=В2+1	=А3*В3
4	=А3+1	=В3+1	=А4*В4
5	=А4+1	=В4+1	=А5*В5
6	=А5+1	=В5+1	=А6*В6
7	=А6+1	=В6+1	=А7*В7
8	=А7+1	=В7+1	=А8*В8
9	=А8+1	=В8+1	=А9*В9

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

Так, при копировании формулы из активной ячейки С1, содержащей абсолютные ссылки на ячейки $А$1 и $В$1, значения столбцов и строк в формуле не изменятся (табл. 1.5).

Таблица 1.5. Абсолютные ссылки

	А	В	С	D	Е
1			=$А$1*$В$1
2				=$А$1*$В$1
3					=$А$1*$В$1

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

Пусть названия устройств размещены в ячейках столбца А, их цены в условных единицах - в ячейках столбца В, цены в рублях будут вычисляться в ячейках столбца С, а значение курса условной единицы к рублю хранится в ячейке Е2. Тогда в ячейку С 2 необходимо ввести формулу =В2*$Е$2, содержащую абсолютную ссылку, и скопировать ее в нижележащие ячейки столбца С (табл. 1.6).

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

	А	В	С	D	Е
1	Устройство	Цена в у.е.	Цена в рублях	Курс доллара к рублю
2	Системная плата	80	=В2*$Е$2	1 у.е.=	29
3	Процессор	70	=ВЗ*$Е$2
4	Оперативная память	15	=В4*$Е$2
5	Жесткий диск	100	=В5*$Е$2
6	Монитор	200	=В6*$Е$2
7	Дисковод 3,5"	12	=В7*$Е$2
8	Дисковод CD-ROM	30	=В8*$Е$2
9	Корпус	25	=В9*$Е$2
10	Клавиатура	10	=В10*$Е$2
11	Мышь	5	=В11*$Е$2
12	ИТОГО:	=СУММ(В2:В11)	=СУММ(С2:С11)

Смешанные ссылки . В формуле можно использовать смешанные ссылки, в которых координата столбца относительная, а строки - абсолютная (например, А$1), или, наоборот, координата столбца абсолютная, а строки - относительная (например, $В1) (табл. 1.7).

Таблица 1.7. Смешанные ссылки

	А	В	С	D	Е
1			=A$1*$B1
2				=B$1*$B2
3					=C$1*$B3

В качестве примера использования в формуле смешанной ссылки можно рассмотреть пересчет цен из условных единиц в рубли по двум курсам (доллара и евро). Пусть в созданной нами таблице цен устройств компьютера в ячейке Е2 хранится курс доллара к рублю, а в ячейке F2 - курс евро к рублю. Тогда в ячейку С2 необходимо ввести формулу =$В2*Е$2, содержащую смешанные ссылки, и скопировать ее в нижележащие ячейки столбца С, а затем - в соседние ячейки столбца D (табл. 1.8).

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

	А	В	С	D	Е	F
1	Устройство	Цена в у.е.	Цена в рублях	Цена в рублях	Курсы у.е.
2	Системная плата	80	=$В2*Е$2	=$В2*F$2	28	36
3	Процессор	70	=$В3*Е$2	=$В3*F$2
4	Оперативная память	15	=$В4*Е$2	=$В4*F$2
5	Жесткий диск	100	=$В5*Е$2	=$В5*F$2
6	Монитор	200	=$В6*Е$2	=$В6*F$2
7	Дисковод 3,5"	12	=$В7*Е$2	=$В7*F$2
8	Дисковод CD-ROM	30	=$В8*Е$2	=$В8*F$2
9	Корпус	25	=$В9*Е$2	=$В9*F$2
10	Клавиатура	10	=$В10*Е$2	=$В10*F$2
11	Мышь	5	=$В11*Е$2	=$В11*F$2
12	ИТОГО:	=СУММ(В2:В11)	=СУММ(С2:С11)	=СУММ(D2:D11)

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

1. Как изменяется при копировании в ячейку, расположенную в соседнем столбце и строке, формула, содержащая относительные ссылки? Абсолютные ссылки? Смешанные ссылки?

Задания для самостоятельного выполнения

2. Задание с кратким ответом. Какой вид приобретут формулы, хранящиеся в диапазоне ячеек С1:СЗ, при их копировании в диапазон ячеек Е2:Е4?

	А	В	С	D	Е
1			=A1+B1
2			=$А$1*$В$1
3			=$А1*В$1
4

3. Практическое задание. Проверьте в электронных таблицах правильность ответов на предыдущее задание.

Встроенные функции

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

Суммирование. Одной из наиболее часто используемых операций является суммирование значений диапазона ячеек. Для этого необходимо выделить диапазон, причем для ячеек, расположенных в одном столбце или строке, достаточно для вызова функции суммирования чисел СУММ() щелкнуть по кнопке Автосумма å на панели инструментов Стандартная .

Результат суммирования будет записан в ячейку, следующую за последней, ячейкой диапазона в столбце (например, =СУММ(А2:А4)), строке (например, =СУММ(С1:Е1)) или прямоугольном диапазоне ячеек (например, =СУММ(СЗ:Е4)) (рис. 1.2).

Рис. 1.2. Суммирование значений диапазонов ячеек

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

Степенная функция. В математике широко используется степенная функция у = х n , где х - аргумент, a n - показатель степени (например, у = х 2 , у = х 3 и т. д.). Ввод функций в формулы можно осуществлять с помощью клавиатуры или с помощью Мастера функций , который предоставляет пользователю возможность вводить функции с использованием последовательностей диалоговых панелей.

Например, если в ячейке В1 хранится значение аргумента х функции, то вид функции, введенной с клавиатуры (ячейка В2), будет =B1^2, а введенной с помощью мастера функций (ячейка ВЗ) - СТЕПЕНЬ(В1;2) (рис. 1.3).

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

Заполнение таблицы можно существенно ускорить, если использовать операцию Заполнить . Сначала в первую ячейку строки аргументов вводится наименьшее значение аргумента (например, в ячейку В1 вводится число -4), а во вторую ячейку вводится формула, вычисляющая следующее значение аргумента с учетом величины шага аргумента (например, =В1+1). Далее эта формула вводится во все остальные ячейки таблицы с использованием операции Заполнить вправо .

Аналогично, в первую ячейку строки значений функции вводится формула вычисления функции (например, в ячейку В2 вводится формула =В1^2), далее эта формула вводится во все остальные ячейки таблицы с использованием операции Заполнить вправо (табл. 1.9).

Таблица 1.9. Числовое представление квадратичной функции у = х 2

	А	В	С	D	Е	F	G	H	I	J
1	x	-4	-3	-2	-1	0	1	2	3	4
2	y = x^2	16	9	4	1	0	1	4	9	16

Задания для самостоятельного выполнения

4. Задание с кратким ответом. Какие значения будут получены в ячейках А5, F1 и F4 после суммирования значений различных диапазонов ячеек (см. рис. 1.2)? Проверить в электронных таблицах.

5. Задание с кратким ответом. Какие значения будут получены в ячейках В2 и ВЗ после вычисления значений степенной функции (см. рис. 1.3)? Проверить в электронных таблицах.

6. Задание с кратким ответом. Какие значения будут получены в ячейках В2 и ВЗ после вычисления значений квадратного корня (см. рис. 1.4)? Проверить в электронных таблицах.

7. Практическое задание. Построить таблицу значений функции у = Ö x. на отрезке с шагом 1.

Тема : Электронные таблицы.

Что нужно знать :

адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15
формулы в электронных таблицах начинаются знаком = («равно»)
знаки +, –, *, / и ^ в формулах означают соответственно сложение, вычитание, умножение, деление и возведение в степень
запись B2:C4 означает диапазон, то есть, все ячейки внутри прямоугольника, ограниченного ячейками B2 и C4:

например, по формуле =СУММ(B2:C4) вычисляется сумма значений ячеек B2, B3, B4, C2, C3 и C4
в заданиях ЕГЭ могут использоваться стандартные функции СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение)
функция СРЗНАЧ при вычислении среднего арифметического не учитывает пустые ячейки и ячейки, заполненные текстом; например, после ввода формулы в C2 появится значение 2 (ячейка А2 – пустая):

функция СЧЕТ(A1:B2) в этом случае выдаст значение 3 (а не 4).

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

в абсолютных адресах перед именем столбца и перед номером строки ставится знак доллара $, такие адреса не изменяются при копировании; вот что будет, если формулу =$B$2+$ C $3 скопировать из D5 во все соседние ячейки

знак $ как бы «фиксирует» значение: в абсолютных адресах и имя столбца, и номер строки зафиксированы

в относительных адресах знаков доллара нет, такие адреса при копировании изменяются: номер столбца (строки) изменяется на столько, на сколько отличается номер столбца (строки), где оказалась скопированная формула, от номера столбца (строки) исходной ячейки; вот что будет, если формулу =B2+ C 3 (в ней оба адреса – относительные) скопировать из D5 во все соседние ячейки:

в смешанных адресах часть адреса (строка или столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть – относительная; относительная часть изменится при копировании так же, как и для относительной ссылки:

Пример задания:

В ячейке B4 электронной таблицы записана формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4 скопируют в ячейку B6? Примечание: знак $ используется для обозначения абсолютной адресации.

1) =$C5*4 2) =$C5*2 3) =$C3*4 4) =$C3*2

Решение:

ссылка $C3 – это смешанная ссылка, в которой «заблокирован» столбец C, а строка 3 – это относительный адрес;
после того, как ячейку B4 скопировали в B6, номер строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная часть) также увеличится на 2, ссылка превратится в $C5
константы при копировании формул не меняются, поэтому получится =$C5*2
таким образом, правильный ответ – 2 .

Ещё пример задания:

Три страны: Королевство Бельгия, Королевство Нидерланды и Великое Герцогство Люксембург образуют экономико-политический союз, который носит название Бенилюкс. Ниже приведен фрагмент электронной таблицы, характеризующий каждую из стран союза и союз в целом:

	А	B	C	D
1	Страна	Население (тыс. чел)	Площадь (кв. км)	Плотность населения (чел / кв.км)
2	Бельгия	10 415	30 528	341
3	Нидерланды	16 357	41 526	394
4	Люксембург	502	2 586	194
5	Бенилюкс в целом	27 274	74 640

Какое значение должно стоять в ячейке D5?

1) 365 2) 929 3) 310 4) 2,74

Решение:

нужно не забыть, что плотность населения вычисляется как отношение населения к площади (не наоборот!);
население не забываем перевести из тысяч человек в единицы: 27 274 000 чел
поэтому для всего Бенилюкса получаем 27 274 000 / 74 640 ≈ 365
таким образом, правильный ответ – 1 .

Еще пример задания:

=СУММ(B1:B2) равно 5. Чему равно значение ячейки B3, если значение формулы =СРЗНАЧ(B1:B3) равно 3?

1) 8 2) 2 3) 3 4) 4

Решение:

функция СУММ(B1:B2) считает сумму значений ячеек B1 и B2, поэтому B1 + B2 = 5
функция СРЗНАЧ(B1:B3) считает среднее арифметическое диапазона B1:B3
строго говоря, такие задачи некорректны, потому что
1. функция СРЗНАЧ учитывает только числовые данные (числа или формулы, при вычислении которых получается число), то есть возможны варианты:

СРЗНАЧ(B1:B3)=СУММ(B1:B3) , если есть только одна числовая ячейка

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/2 , если есть две числовых ячейки

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/3 , если все три ячейки – числовые

в условии не задано, сколько числовых ячеек в диапазоне B1:B3

в такой ситуации логичнее всего считать, что все три ячейки содержат числовые данные (во всех известных автору задачах такого типа используется именно это допущение)
итак, в диапазон B1:B3 входят три ячейки; предполагаем, что все они содержат числовые данные, тогда среднее арифметическое – это сумма их значений, деленная на 3; таким образом B1 + B2 + B3 = 3 · 3 = 9
поскольку B1 + B2 = 5, сразу получаем B3 = 9 – 5 = 4
таким образом, правильный ответ – 4.

Еще пример задания:

	А	В	С
1	10	20	= A1+B$1
2	30	40

Чему станет равным значение ячейки С2, если в нее скопировать формулу из ячейки С1? Знак $ обозначает абсолютную адресацию.

1) 40 2) 50 3)60 4) 70

Решение:

это задача на использование абсолютных и относительных адресов в электронных таблицах
вспомним, что при копировании все относительные адреса меняются (согласно направлению перемещения формулы), а абсолютные – нет
в формуле, которая находится в C1, используются два адреса: A1 и B$1
адрес A1 – относительный, он может изменяться полностью (и строка, и столбец)
адрес B$1 – смешанный, в нем номер строки «зафиксирован» знаком доллара, а имя столбца – нет, поэтому при копировании может измениться только имя столбца
при копировании из C1 в C2 столбец не изменяется, а номер строки увеличивается на 1, поэтому в C2 получим формулу = A 2+ B $1 (здесь учтено, что у второго адреса номер строки «зафиксирован»)
сумма ячеек A2 и B1 равна 30 + 20 = 50

Еще пример задания:

	А	В	С
1	1	2
2	2	6	=СЧЁТ(A1:B2)
3			=СРЗНАЧ(A1:C2)

Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1)

1) –2 2) –1 3) 0 4) +1

Решение:

это задача на знание особенностей функций СЧЕТ и СРЗНАЧ, которые не учитывают пустые ячейки
после ввода формул в С2 окажется количество непустых ячеек диапазона А1:В2, равное 4

(1+2+2+6+4)/5 = 3

после перемещения (не копирования!) содержимого ячейки В2 в В3 ячейка В2 окажется пустой, поэтому в С2 выводится число 3 – количество непустых ячеек диапазона А1:В2
в С3 будет выведено среднее значение диапазона А1:С2 равное

(1+2+2+3)/4 = 2,

то есть значение С3 уменьшится на 1

таким образом, правильный ответ – 2.

Задачи для тренировки 1:

В ячейке B1 записана формула =2*$A1 . Какой вид приобретет формула, после того как ячейку B1 скопируют в ячейку C2?

1) =2*$B1 2) =2*$A2 3) =3*$A2 4) =3*$B2Н

В ячейке C2 записана формула =$E$3+D2 . Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2

Дан фрагмент электронной таблицы:

	A	B	C	D
1	5	2	4
2	10	1	6

В ячейку D2 введена формула =А2*В1+С1 . В результате в ячейке D2 появится значение:

1) 6 2) 14 3) 16 4) 24

В ячейке А1 электронной таблицы записана формула =D1-$D2 . Какой вид приобретет формула после того, как ячейку А1 скопируют в ячейку В1?

1) =E1-$E2 2) =E1-$D2 3) =E2-$D2 4) =D1-$E2

Дан фрагмент электронной таблицы:

	А	В	С	D
1	1	2	3
2	4	5	6
3	7	8	9

В ячейку D1 введена формула =$А$1*В1+С2 , а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

1) 10 2) 14 3) 16 4) 24

В ячейке В2 записана формула =$D$2+Е2 . Какой вид будет иметь формула, если ячейку В2 скопировать в ячейку А1?

1) =$D $ 2+E1 2) =$D$2+C2 3) =$D$2+D2 4) =$D$2+D1

В ячейке СЗ электронной таблицы записана формуле =$А$1+В1 . Какой вид будет иметь формула, если ячейку СЗ скопировать в ячейку ВЗ?

1) =$A$1+А1 2) =$В$1+ВЗ 3) =$А$1+ВЗ 4) =$B$1+C1

При работе с электронной таблицей в ячейке ЕЗ записана формула =В2+$СЗ . Какой вид приобретет формула после того, как ячейку ЕЗ скопируют в ячейку D2?

1) =А1+$СЗ 2) =А1+$С2 3) =E2+$D2 4) =D2+$E2

В ячейке электронной таблицы В4 записана формула =С2+$A$2 . Какой вид приобретет формула, если ячейку В4 скопировать в ячейку С5?

1) =D2+$В$3 2) =С5+$A$2 3) =D3+$A$2 4) =СЗ+$А$3

В ячейке электронной таблицы А1 записана формула =$D1+D$2 . Какой вид приобретет формула, если ячейку А1 скопировать в ячейку ВЗ?

1) =D1+$E2 2) =D3+$F2 3) =E2+D$2 4) =$D3+Е$2

Дан фрагмент электронной таблицы:

	А	В	С
1	2	3
2	4	5	=СЧЁТ(A1:B2)
3			=СРЗНАЧ(A1:C2)

Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1):

1) –1 2) –0,6 3) 0 4) +0,6

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно (-2 ). Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно 5?

1) 1 2) -1 3) -3 4) 7

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно 0,1. Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно (–1)?

1) – 0,7 2) - 0,4 3) 0,9 4) 1,1

В электронной таблице значение формулы =СРЗНАЧ(B 5: E 5) равно 100. Чему равно значение формулы =СУММ(B 5: F 5) , если значение ячейки F5 равно 10?

1) 90 2) 110 3) 310 4) 410

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно 2 . Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно -5?

1) 1 2) -1 3) -3 4) 7

В электронной таблице значение формулы =СУММ(C 3: E 3) равно 15. Чему равно значение формулы =СРЗНАЧ(C 3: F 3) , если значение ячейки F3 равно 5?

1) 20 2) 10 3) 5 4) 4

В динамической (электронной) таблице приведены значения пробега автомашин (в км) и общего расхода дизельного топлива (в литрах) в четырех автохозяйствах с 12 по 15 июля.

	12 июля		13 июля		14 июля		15 июля		За четыре дня
Название автохозяйства	Пробег	Расход	Пробег	Расход	Пробег	Расход	Пробег	Расход	Пробег	Расход
Автоколонна №11	9989	2134	9789	2056	9234	2198	9878	2031	38890	8419
Грузовое такси	490	101	987	215	487	112	978	203	2942	631
Автобаза №6	1076	147	2111	297	4021	587	1032	143	8240	1174
Трансавтопарк	998	151	2054	299	3989	601	1023	149	8064	1200

В каком из хозяйств средний расход топлива на 100 км пути за эти четыре дня наименьший?

1) Автоколонна № 11

2) Грузовое такси

3) Автобаза №6

4) Трансавтопарк

В электронной таблице значение формулы =СРЗНАЧ(A 1: C 1) равно 5. Чему равно значение ячейки D1, если значение формулы =СУММ(A 1: D 1) равно 7?

1) 2 2) -8 3) 8 4) -3

В электронной таблице значение формулы =СРЗНАЧ(B 1: D 1) равно 4. Чему равно значение ячейки A1, если значение формулы =СУММ(A 1: D 1) равно 9?

1) -3 2) 5 3) 1 4) 3

В электронной таблице значение формулы =СРЗНАЧ(A 1: B 4) равно 3. Чему равно значение ячейки A4, если значение формулы =СУММ(A 1: B 3) равно 30, а значение ячейки B4 равно 5?

1) -11 2) 11 3) 4 4) -9

=СУММ(B1: C 4)+F2* E 4– A 3

	A	B	C	D	E	F
1	1	3	4	8	2	0
2	4	–5	–2	1	5	5
3	5	5	5	5	5	5
4	2	3	1	4	4	2

1) 19 2) 29 3) 31 4) 71

На рисунке приведен фрагмент электронной таблицы. Определите, чему будет равно значение, вычисленное по следующей формуле =СУММ(A1:C2)*F4*E2-D3

	A	B	C	D	E	F
1	1	3	4	8	2	0
2	4	–5	–2	1	5	5
3	5	5	5	5	5	5
4	2	3	1	4	4	2

1) –15 2) 0 3) 45 4) 55

В электронной таблице значение формулы =СРЗНАЧ(A 4: C 4) =СУММ(A 4: D 4) , если значение ячейки D4 равно 6?

1) 1 2) 11 3) 16 4) 21

В электронной таблице значение формулы =СРЗНАЧ(A 3: D 4) равно 5. Чему равно значение формулы =СРЗНАЧ(A 3: C 4) , если значение формулы =СУММ(D 3: D 4) равно 4?

1) 1 2) 3 3) 4 4) 6

В электронной таблице значение формулы =СРЗНАЧ(C 2: D 5) равно 3. Чему равно значение формулы =СУММ(C 5: D 5) , если значение формулы =СРЗНАЧ(C 2:D4) равно 5?

1) –6 2) –4 3) 2 4) 4

В динамической (электронной) таблице приведены значения посевных площадей (в га) и урожай (в центнерах).

Зерновые культуры	Заря		Первомайское		Победа		Рассвет
Зерновые культуры	Посевы	Урожай	Посевы	Урожай	Посевы	Урожай	Посевы	Урожай
Пшеница	600	15600	900	23400	300	7500	1200	31200
Рожь	100	2200	500	11000	50	1100	250	5500
Овёс	100	2400	400	9600	50	1200	200	4800
Ячмень	200	6000	200	6000	100	3100	350	10500
Всего	1000	26200	2000	50000	500	12900	2000	52000

В каком из хозяйств достигнута максимальная урожайность зерновых (по валовому сбору, в центнерах с гектара)?

1) Заря 2) Первомайское 3) Победа 4) Рассвет

Дан фрагмент электронной таблицы:

	B	C	D
69	5	10
70	6	9	=СЧЁТ(B69:C70)
71			=СРЗНАЧ(B69:D70)

После перемещения содержимого ячейки C70 в ячейку C71 значение в ячейке D71 изменится по абсолютной величине на:

1) 2,2 2) 2,0 3) 1,05 4) 0,8

Дан фрагмент электронной таблицы:

	B	C	D
69	5	10
70	6	9	=СЧЁТ(B69:C70)
71			=СРЗНАЧ(B69:D70)

После перемещения содержимого ячейки B69 в ячейку D69 значение в ячейке D71 изменится по сравнению с предыдущим значением на:

1) –0,2 2) 0 3) 1,03 4) –1,3

В динамической (электронной) таблице приведены данные о продаже путевок турфирмой «Все на отдых» за 4 месяца. Для каждого месяца вычислено общее количество проданных путевок и средняя цена одной путевки.

Страна	май		июнь		июль		август
Страна	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.
Египет	12	24	15	25	10	22	10	25
Турция	13	27	16	27	12	26	11	28
ОАЭ	12	19	12	22	10	21	9	22
Хорватия	5	30	7	34	13	35	10	33
Продано, шт.	42		50		45		40
Средняя цена, тыс.руб.	25		27		26		27

Известно, что доход фирмы от продажи каждой путевки не зависит от места отдыха и равен 10% от средней цены путевки в текущем месяце. В каком месяце доход турфирмы был максимальный?

В электронной таблице значение формулы =СРЗНАЧ(D1: D 4) равно 8. Чему равно значение формулы =СРЗНАЧ(D 2: D 4) , если значение ячейки D1 равно 11?

1) 19 2) 21 3) 7 4) 32

На рисунке приведен фрагмент электронной таблицы. В ячейку B2 записали формулу =($A2*10+B$1)^2 и скопировали ее вниз на 2 строчки, в ячейки B3 и B4. Какое число появится в ячейке B4?

	A	B	C	D
1		0	1	1
2	1
3	2
4	3
5

1) 144 2) 300 3) 900 4) 90

На рисунке приведен фрагмент электронной таблицы. Чему будет равно значение ячейки B4, в которую записали формулу =СУММ(A 1: B 2; C 3) ?

	A	B	C	D
1	1	2	3
2	4	5	6
3	7	8	8
4

1) 14 2) 15 3) 17 4) 20

В ячейке электронной таблицы С3 записана формула = B 2+$ D $3- E $2 . Какой вид приобретет формула, если ячейку C3 скопировать в ячейку С4?

1) =B3+$G$3-E$2 2) =B3+$D$3-E$3
3) =B3+$D$3-E$2 4) =B3+$D$3-F$2

На рисунке приведен фрагмент электронной таблицы. В ячейку D3 введена формула = B 2+$ B 3-$ A $1 . Какое число появится в ячейке C4, если скопировать в нее формулу из ячейки D3?

	A	B	C	D
1	5	10
2	6	12
3	7	14
4	8	16

1) 8 2) 18 3) 21 4) 26

1 Источники заданий:

Демонстрационные варианты ЕГЭ 2004-2011 гг.
Гусева И.Ю. ЕГЭ. Информатика: раздаточный материал тренировочных тестов. - СПб: Тригон, 2009.
Крылов С.С., Ушаков Д.М. ЕГЭ 2010. Информатика. Тематическая рабочая тетрадь. - М.: Экзамен, 2010.
Якушкин П.А., Ушаков Д.М. Самое полное издание типовых вариантов реальных заданий ЕГЭ 2010. Информатика. - М.: Астрель, 2009.
М.Э. Абрамян, С.С. Михалкович, Я.М. Русанова, М.И. Чердынцева. Информатика. ЕГЭ шаг за шагом. – М.: НИИ школьных технологий, 2010.
Чуркина Т.Е. ЕГЭ 2011. Информатика. Тематические тренировочные задания. - М.: Эксмо, 2010.
Якушкин П.А., Лещинер В.Р., Кириенко Д.П. ЕГЭ 2011. Информатика. Типовые тестовые задания. - М.: Экзамен, 2011.
Самылкина Н.Н., Островская Е.М. ЕГЭ 2011. Информатика. Тематические тренировочные задания. - М.: Эксмо, 2010.

http://kpolyakov.narod.ru

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

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

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

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

Абсолютные ссылки отличаются от относительных тем, что при копировании не изменяются. Записываются они со знаком «$». Если содержимое ячейки используется в формуле как константа, то, при расчете по этой формуле таблицы значений, в ссылке на данную ячейку обозначение столбца заключается в «$», например S/Ш (значение хранится в ячейке А2).

Используем первую строку для обозначения величин, используемых в данном примере, а во вторую строку и ниже будем помещать соответствующие числегшые значения. Пусть в ячейке А2 хранится значение константы а, в ячейке В2 - значение Ь, а диапазону С2:С7 соответствуют значения переменной х. Вычисляемые значения величины у будем помещать в столбце D (диапазон D2.D1). Для вычисления величины у выделим ячейку D2 и начнем внесение формулы со знака «=», далее выделим ячейку А2, затем поставим знак «+», выделим ячейку В2, поставим знак «*», выделим ячейку С2 и нажмем клавишу Enter. В ячейке D2 появится число 5,9, а в строке формул останется запись: =А2+В2*С2. Поскольку формула будет использоваться для вычисления диапазона значений у для соответствующего диапазона х, адреса ячеек, в которых хранятся константы, должны быть зафиксированы, т. е. в обозначение ячеек /12 и #2 нужно внести символы «5», таким образом формула в строке формул примет вид, представленный на рис. 9.4-9.5.

В ячейках появятся вычисленные значения (см. рис. 9.5).

Такая процедура называется Автозаполнением ячеек.

Для расчетов в документах можно использовать и смешанные ссылки. Например, = $41 или=/4$1. Знак $ не позволяет меняться параметру, перед которым он поставлен. Если знак поставлен пред названием строки, то не меняется номер строки, если перед столбцом, то не меняется название столбца.

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

Рис. 9.4.

Рис. 9.5.