Созданиеформул для обработки данных в электронной таблице Excel
Программа Microsoft Excel предназначена для работы с таблицами данных,преимущественно числовых. При формировании таблицы выполняют ввод, редактированиеи форматирование текстовых и числовых данных, а также формул. Наличие средствавтоматизации облегчает эти операции. Созданная таблица может быть выведена напечать.
Ввод, редактирование и форматирование данных. Отдельнаяячейка может содержать данные, относящиеся к одному из трех типов: текст, число или формула, — а такжеоставаться пустой. Программа Excel при сохранении рабочей книгизаписывает в файл только прямоугольную область рабочих листов, примыкающую клевому верхнему углу (ячейка А1) и содержащую все заполненные ячейки.
Тип данных, размещаемых в ячейке, определяется автоматически при вводе.Если эти данные можно интерпретировать как число, программа Excel так иделает. В противном случае данные рассматриваются как текст. Ввод формулывсегда начинается с символа «=» (знака равенства).
Ввод текста и чисел. Ввод данныхосуществляют непосредственно в текущую ячейку или в строку формул, располагающуюсяв верхней части окна программы под панелями инструментов (см. рис. 2). Местоввода отмечается текстовым курсором. Если начать ввод нажатиемалфавитно-цифровых клавиш, данные из текущей ячейки заменяются вводимымтекстом. Если щелкнуть на строке формул или дважды на текущей ячейке, староесодержимое ячейки не удаляется и появляется возможность его редактирования.Вводимые данные в любом случае отображаются как в ячейке, так и в строкеформул.
Чтобы завершить ввод, сохранив введенные данные, используют кнопку Вводв строке формул или клавишу ENTER. Чтобы отменить внесенные изменения ивосстановить прежнее значение ячейки, используют кнопку Отмена в строке формул иликлавишу ESC. Для очистки текущей ячейки или выделенного диапазона проще всегоиспользовать клавишу DELETE.
Форматирование содержимого ячеек. Текстовые данные по умолчаниювыравниваются по левому краю ячейки, а числа — по правому. Чтобы изменитьформат отображения данных в текущей ячейке или выбранном диапазоне, используюткоманду Формат>Ячейки. Вкладки этого диалогового окна позволяют выбиратьформат записи данных (количество знаков после запятой, указание денежнойединицы, способ записи даты и прочее), задавать направление текста и метод еговыравнивания, определять шрифт и начертание символов, управлять отображением ивидом рамок, задавать фоновый цвет.
Формулы. Вычисления в таблицах программы Excel осуществляютсяпри помощи формул. Формуламожет содержать числовые константы, ссылки на ячейки и функции Excel, соединенныезнаками математических операций. Скобки позволяют изменять стандартный порядоквыполнения действий. Если ячейка содержит формулу, то в рабочем листеотображается текущий результат вычисления этой формулы. Если сделать ячейкутекущей, то сама формула отображается в строке формул.
Правило использования формул в программе Excel состоит в том, что, еслизначение ячейки действительно зависит от других ячеектаблицы, всегда следует использовать формулу, даже еслиоперацию легко можно выполнить в «уме». Это гарантирует, что последующеередактирование таблицы не нарушит ее целостности и правильности производимых вней вычислений.
Ссылки на ячейки. Формула может содержать ссылки, то естьадреса ячеек, содержимое которых используется в вычислениях. Это означает, чторезультат вычисления формулы зависит от числа, находящегося в другой ячейке.Ячейка, содержащая формулу, таким образом, является зависимой. Значение,отображаемое в ячейке с формулой, пересчитывается при изменении значенияячейки, на которую указывает ссылка.
Ссылку на ячейку можно задать разными способами. Во-первых, адресячейки можно ввести вручную. Другой способ состоит в щелчке на нужной ячейкеили выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон приэтом выделяются пунктирной рамкой.
Все диалоговые окна программы Excel, которыетребуют указания номеров или диапазонов ячеек, содержат кнопки, присоединенныек соответствующим полям. При щелчке на такой кнопке диалоговое окносворачивается до минимально возможного размера, что облегчает выбор нужнойячейки (диапазона) с помощью щелчка или протягивания (рис. 3).
/>
Рис. 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 приведены правила обновления ссылок при автозаполнениивдоль строки или вдоль столбца.
Таблица
Правила обновления ссылок при автозаполненииСсылка в исходной ячейке Ссылка в следующей ячейке При заполнении вправо При заполнении вниз А1 (относительная) В1 А2 $А1 (абсолютная по столбцу) $А1 $А2 А$1 (абсолютная по строке) В$1 А$1 $A$1 (абсолютная) $А$1 $А$1
Использование стандартных функций. Стандартные функциииспользуются в программе Excel только в формулах. Вызов функции состоитв указании в формуле имени функции, после которого в скобкахуказывается списокпараметров. Отдельные параметры разделяются в списке точкойс запятой. В качестве параметра может использоваться число, адрес ячейки илипроизвольное выражение, для вычисления которого также могут использоватьсяфункции,
В режиме ввода формулы в левой части строки формул, где раньшерасполагался номер текущей ячейки, появляется раскрывающийся список функций. Онсодержит десять функций, которые использовались последними, а также пунктДругие функции.
Использование мастера функций. При выборе пункта Другиефункции запускается Мастер функций, облегчающий выбор нужной функции. Враскрывающемся списке Категория выбирается категория, к которой относитсяфункция (если определить категорию затруднительно, используют пункт Полныйалфавитный перечень), а в списке Выберите функцию — конкретная функция даннойкатегории. После щелчка на кнопке ОК имя функции заносится в строку формулвместе со скобками, ограничивающими список параметров. Текстовый курсорустанавливается между этими скобками. Вызвать Мастер функций можно и проще,щелчком на кнопке Вставка функции в строке формул.
Аргументы функции. Как только имя функции выбрано, на экранепоявляется диалоговое окно Аргументы функции (в предыдущих версиях Excel этоокно рассматривалось как палитра формул). Это окно, в частности, содержитзначение, которое получится, если немедленно закончить ввод формулы (рис. 4).
Правила вычисления формул, содержащих функции, не отличаются от правилвычисления более простых формул. Ссылки на ячейки, используемые в качествепараметров функции, также могут быть относительными или абсолютными, чтоучитывается при копировании формул методом автозаполнения.
/>
Рис. 4. Строка формул и диалоговое окно Аргументы функции