Обработка данных в электронных таблицах

Технология обработки информации в электронных таблицах

Ввод и редактирование данных в электронных таблицах, операции над данными

Программа Excel — одно из самых популярных средств управления электронными таблицами.

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

Таблица состоит из столбцов и строк. Столбцы обозначаются латинскими буквами A, B, C, D и т. д. Если букв алфавита не хватает — используют двухбуквенные обозначения AA, AB, AC,…, IV. Максимальное число столбцов — 256. Строки нумеруются целыми числами. Максимальное число строк — 65535.

Ячейки располагаются на пересечении столбцов и строк. Номер (адрес) ячейки формируется из номера столбца и номера строки без пробела между ними. Например, A1, D24, M355. Одна из ячеек на рабочем листе всегда является текущей (активной). Она обведена широкой рамкой; в поле имени выводится ее номер, а в строке формул — ее содержимое. Именно с этой ячейкой производится работа — считывание или запись.

Заполнение таблиц данными

Ячейка может содержать три вида данных:

1. Текстовые данные — строка текста произвольной длины.
2. Числовые данные — отдельное число (в допустимом формате). Как числа рассматриваются данные, определяющие даты или денежные суммы.
3. Формулы — содержимое ячейки рассматривается как формула, если оно начинается со знака равенства «=». Ячейка, содержащая формулу, является вычисляемой, т. е. значение ее может зависеть от значений других ячеек таблицы. Формулы в ячейках не отображаются. Вместо формулы воспроизводится результат, полученный при ее вычислении. Чтобы увидеть формулу, хранящуюся в вычисляемой ячейке, нужно выделить эту ячейку (сделать текущей) и посмотреть в строку формул.

Возможна одновременная обработка нескольких ячеек (диапазона ячеек). Диапазон отображается на экране в инвертированном виде и обводится толстой рамкой. Бывают двумерные (строка, столбец) и трехмерные (строка, столбец, лист) диапазоны. Для обозначения диапазона используется разделитель « : ». Например, В:Е — диапазон столбцов от В до Е включительно, т. е. вертикальная полоса; 2:6 — диапазон строк со 2 по 6 включительно, т. е. горизонтальная полоса; B2:G12 — диапазон ячеек, левый верхний угол которого — ячейка В2, нижний правый угол — ячейка G12.

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

Редактирование введенных данных осуществляется следующим образом. Если в заполненную ячейку нужно ввести новые данные, то необходимо сделать ее текущей и начать вводить новые значения. Старое содержимое ячейки при этом утрачивается. Если же надо только откорректировать некоторые данные, то нужно, активизировав ячейку, нажать клавишу [F2] или выполнить двойной щелчок левой клавишей мыши по ячейке. При этом в ячейке появится текстовый курсор, и содержимое можно исправить. Также исправления можно ввести в строку формул активной ячейки.

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

Чтобы размножить данные на несколько ячеек, нужно сделать активной первую ячейку диапазона и заполнить ее. Затем установить указатель мыши на маркер заполнения (правый нижний угол рамки текущей ячейки). Перетаскиванием маркера заполнения можно размножить содержимое текущей ячейки на несколько ячеек в столбце или строке. Если в первой ячейке были введены день недели или дата, то при перетаскивании вправо или вниз их значения будут увеличиваться на 1, а при перетаскивании влево или вверх — уменьшаться на 1. Если в ячейку было введено число или текст, то при перетаскивании маркера оно скопируется в другие ячейки. Для создания числового ряда при перетаскивании маркера заполнения следует удерживать нажатой клавишу [Ctrl]. По ходу перетаскивания содержимое последней ячейки отображается в небольшом всплывающем окне. Если для перетаскивания маркера использовать правую кнопку мыши, то при ее отпускании появится контекстное меню, позволяющее изменить способ заполнения диапазона.

Если требуется более сложный закон изменения последовательности значений, то после выбора первой ячейки и ввода в нее нужного значения надо выполнить команду Заполнить/Прогрессия из группы Редактирование вкладки Главная.

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

Операции над данными

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

Удаление. При нажатии клавиши [Del] произойдет очистка содержимого ячейки. Реальное удаление ячейки сопровождается изменением структуры таблицы. По команде Удалить ячейки из группы Ячейки вкладки Главная откроется диалоговое окно Удаление ячеек, в котором нужно выбрать направление смещения ячеек, занимающих освобождающееся место. Аналогичное окно открывается и при выборе команды Удалить из контекстного меню ячейки. Для удаления строки (столбца) надо выделить какую-либо ячейку в удаляемой строке (столбце) и выполнить команду Удалить строки с листа (Удалить столбцы с листа) из группы Ячейки вкладки Главная. Удалить строку или столбец целиком можно также выделив строку или столбец щелчком мыши по маркеру строки или столбца и выбрав команду Удалить из контекстного меню.

Копирование или перемещение. Эти операции можно осуществить разными способами:

1. Навести указатель мыши на границу текущей ячейки (диапазона) и перетащить при нажатой левой кнопке мыши на новое место. В результате произойдет перемещение ячейки (диапазона). Если при перетаскивании удерживать нажатой клавишу [Ctrl], то произойдет копирование ячейки. При перетаскивании появляется серая рамка, которая помогает правильно разместить блок на новом месте.
2. Выделить ячейку (диапазон), перейти на вкладку Главная и выполнить команду Копировать или Вырезать из группы Буфер обмена, а затем щелкнуть в нужном месте и выполнить команду Вставить .
3. Выполнить специальное перетаскивание нужного блока ячеек (нажата правая кнопка мыши). При отпускании кнопки мыши будет выведено контекстное меню, содержащее перечень допустимых операций: копирование, перемещение, копирование только значений или только форматов, копирование или перемещение со сдвигом остальных ячеек в нужную сторону.

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

Если нужно скопировать только часть информации, можно воспользоваться специальной вставкой. Команда Вставить/Специальная вставка из группы Буфер обмена откроет диалоговое окно, в котором можно указать, какую часть информации следует вставить из Буфера обмена (например, только значения без формул, по которым они получены; только форматы или только примечания).

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

Отформатировать ячейку (выделенный диапазон ячеек) и ее содержимое можно с помощью параметров групп Шрифт, Выравнивание, Число и Стили вкладки Главная. Щелчок мышью на кнопке с изображением стрелки рядом с названием группы открывает диалоговое окно Формат ячеек. На вкладке Число этого окна можно выбрать формат представления чисел и задать различные параметры, зависящие от выбранного формата; на вкладке Выравнивание установить способ вертикального и горизонтального выравнивания данных в ячейке, задать перенос по словам, объединение ячеек, автоподбор их ширины и т. д.; с помощью команд вкладки Шрифт задать гарнитуру шрифта и его параметров; на вкладках Граница и Вид задать способ обрамления ячейки, выбрать цвет фона и узор заполнения; с помощью команд вкладки Защита защитить ячейку паролем.

Типы и формат данных. Работа с формулами. Абсолютная и относительная ссылки. Использование функций

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

Числа в Excel отображаются в таких категориях форматов, как Числовой, Экспоненциальный, Финансовый, Денежный, Процентный и Дробный. Если необходимо ввести числа без привязки к каким-либо форматам, можно использовать формат Общий. При использовании числового формата можно задавать количество десятичных знаков с помощью кнопок Формат с разделителями, Увеличить разрядность, Уменьшить разрядность группы Число. Перевести число в процентный формат позволяет кнопка, а в денежный — кнопка.

Формат Дата и время позволяет хранить значения временных данных в удобном и привычном виде, например: 16.4; 16 апр; 16 Апрель 2009; апрель 2009; 16.04.09 1:30 РМ; 16.04.09 13:30 и т. д.

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

Формула должна начинаться со знака равенства и может включать в себя числа, имена ячеек, функции и знаки математических операций («+» — сложение; «–» — вычитание; «» — умножение; «/» — деление, «^» — возведение в степень).

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

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

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например: «на две строки выше». Абсолютная ссылка использует для указания на ячейку ее фиксированное положение на листе, например: «ячейка находится в столбце А строки 2». Смешанная ссылка содержит относительную и абсолютную ссылки, например: «ячейка находится в столбце А и выше на две строки». Относительная ссылка на ячейку А1 записывается так: = А1.

Абсолютная ссылка на ячейку А1 имеет следующий вид: = $A$1.

Комбинируя абсолютные и относительные ссылки на ячейку А1, можно создать следующие смешанные ссылки: = $A1 = A$1.

Если символ "доллар" стоит перед буквой, то координата столбца абсолютная, а строки — относительная. Если же символ "доллар" стоит перед числом, то координата столбца относительная, а строки — абсолютная. С помощью клавиши [F4] можно быстро изменить тип ссылки по схеме: A1 → $A$1→ A$1 → $A1 → A1.

При перемещении или копировании формулы из активной ячейки относительные ссылки автоматически настраиваются, а абсолютные сохраняются. Например, если в ячейку С2 ввести формулу = А2 * В2 + $A$1, а затем перетащить маркер заполнения до ячейки С6, то в ячейках С3—С6 относительные ссылки А1 и В1 будут заменяться на А3, В3, А4, В4 и т. д., а ссылка $A$1 не изменится.

Формулы с текстовыми значениями

В большинстве случаев работа с текстовыми значениями происходит так же, как с числами. Например, если ячейка А1 содержит текст ABCDE и в ячейку А10 введена формула = А1, то в ячейке А10 также будет выведено ABCDE.

Для объединения текстовых значений используется текстовый оператор &. Например, если ячейка А1 содержит текст ABCDE, а ячейка А2 — текст WQRT, и в ячейку А3 введена формула = А1 & A2, то в ячейке А3 будет выведено ABCDEWQRT. Чтобы вставить пробел между двумя строками, нужно изменить формулу на следующую: = А1&" "&A2 && . Эта формула использует два текстовых оператора и текстовую константу — в данном случае пробел, заключенный в двойные кавычки.

С помощью оператора & можно объединять и числовые значения. Например, если ячейка А1 содержит числовое значение 123 и в ячейке А2 записано число 456, значением формулы =А1&А2 будет строка символов 123456. Эта строка выравнивается по левому краю, т. к. она является текстовым значением.

Кроме того, можно использовать оператор & для объединения текстовых и числовых значений. Например, если ячейка А1 содержит текст ABCDE, а ячейка А2 содержит числовое значение 123, то значением формулы = А1&А2 будет строка ABCDE123.

Если Excel не может вычислить результат формулы, то в ячейку выводится ошибочное значение, например: #Дел/0!, #ИМЯ?, #ЗНАЧ! и др.

Примеры решения задач

Пример 1. В ячейке С1 записана формула = 3 * $B1. Формулу скопировали в ячейку Е2. Какой вид приобретет формула в ячейке Е2?

Решение. В формуле используется смешанная ссылка на ячейку $B1: ссылка на столбец абсолютная, т. е. при копировании столбец не изменится; ссылка на строку относительная, т. е. при копировании номер строки настроится. В исходной формуле в качестве второго сомножителя выступают данные ячейки из той же строки, в которой находится и формула (из 1-й). Значит, и в скопированной формуле данные будут браться из той же строки (2-й).

Ответ: в ячейке Е2 будет записана формула = 3 * $B2.

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

В ячейку D2 введена формула \ А2 * В1 + $C$1. Какое значение появится в ячейке D2 после вычисления формулы?

Решение. Значение ячейки А2 — 3, В1 — 5, $C$1 — 3. Подставим значения в формулу: 3 · 5 + 3 = 18.

Ответ: 18.

Использование функций

Функция — это заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает одно или несколько значений.

Одно из самых распространенных действий — вычисление суммы значений в столбцах или строках. Excel предлагает функцию =СУММ (диапазон). Например, для суммирования значений в ячейках А1:А10 в ячейку А11 нужно ввести функцию =СУММ (А1:А10). Для облегчения доступа к этой функции в группе Редактирование вкладки Главная есть кнопка Сумма. Чтобы просуммировать значения в столбце (строке) надо выделить ячейку ниже суммируемых чисел (справа) и нажать кнопку Сумма.

Функция состоит из двух частей:

1. имя (СУММ, AVERAGE и др.) — описывает операцию, которую выполняет функция;
2. аргументы — задают значения или ячейки, используемые функцией.

Например, СУММ (А1:А6). Здесь СУММ — имя, а диапазон М А1:А6 — ее единственный аргумент.

Между именем функции и ее аргументами не должно быть пробелов. Некоторые функции не имеют аргументов, но круглые скобки все равно нужно ставить. Например: =А1 ПИ().

При использовании в функции нескольких аргументов нужно отделять их друг от друга « ; ». Например: функция =ПРОИЗВ(С1;С2;С5) перемножает числа в ячейках С1, С2 и С5.

В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символа. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция =СУММ (А1:А5; С2:С10; D3:D17) содержит всего три аргумента, но суммирует 29 ячеек. Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки и диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.

Аргументы функций могут представлять собой выражения из комбинаций функций. Например: =СУММ (SIN (A1ПИ ( ) ); 2 * COS (A2ПИ ( ) ) ). Здесь функции SIN, COS и ПИ сначала вычисляются, а затем используются в качестве аргументов функции СУММ.

Функцию можно вводить на рабочий лист прямо с клавиатуры. Но удобнее воспользоваться командой Вставить функцию из группы Библиотека функций вкладки Формулы. Excel выведет окно диалога Мастер функций — шаг 1 из 2. В этом окне сначала надо выбрать категорию в одноименном списке, а затем в алфавитном списке Выберите функцию указать нужную функцию и нажать кнопку ОК. Excel выведет в ячейке и в строке формул знак равенства, имя функции и пару круглых скобок, а затем откроет второе окно диалога мастера функций. В нем содержатся поля для ввода аргументов выбранной функции. Если функция имеет переменное число аргументов, то это окно диалога увеличивается при вводе дополнительных аргументов. Справа от каждого поля аргумента отображается его текущее значение. После ввода всех аргументов надо нажать кнопку ОКК — и созданная функция появится в строке формул.

Также окно мастера функций можно вызвать щелчком на кнопке Вставить функцию в строке формул. Кроме того, если ввести знак равенства в ячейку, то поле Имя в строке формул заменится раскрывающимся списком, в котором перечислены 10 функций, использовавшихся последними, и пункт Другие функции, щелчок на котором откроет окно мастера функций. Еще один способ вызова мастера функций — в группе Редактирование вкладки Главная раскрыть список опций параметра Сумма и выбрать пункт Другие функции.

Всего в Excel более 300 встроенных функций. Вот некоторые из них.

Математические функции:

= СУММ (числа) — функция суммирует множество чисел.

= ABS (число) — возвращает абсолютное значение числа или формулы.

= ОКРУГЛ (число; количество_цифр) — округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов.

= КОРЕНЬ (число) — возвращает положительный квадратный корень из числа. Аргумент должен быть положительным числом.

= ПРОИЗВЕД (число1; число2;…) — перемножает все числа, задаваемые ее аргументами.

= LOG (число; основание) — возвращает логарифм положительного числа по заданному основанию. Если аргумент основание не задан, то Excel примет его равным 10.

= СТЕПЕНЬ (число; степень) — возводит число в заданную степень. Эта функция является эквивалентом оператора ^. Например, формулы =СТЕПЕНЬ(3;2) и =3^2 равносильны.

= SIN (число), = COS (число), = TAN (число) — возвращают синус, косинус, тангенс угла. Число — это угол в радианах.

Текстовые функции:

= ЗНАЧЕН (текст) — преобразование текста в число. Текст — это числа или даты, введенные в текстовом формате, т. е. заключенные в кавычки.

= ДЛСТР (текст) — возвращает количество символов в текстовой строке.

= СИМВОЛ (число) — возвращает символ, который соответствует заданному числовому коду ASCII.

= КОДСИМВ (текст) — возвращает код ASCII для первого символа аргумента.

Логические функции:

= ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь). В качестве аргументов этой функции можно использовать другие функции.

= И (логическое_значение1; логическое_значение2; …; логическое_значение30). Принимает значение ИСТИНА тогда и только тогда, когда все аргументы имеют значение ИСТИНА.

= ИЛИ(логическое_значение1; логическое_значение2; …; логическое_значение30). Принимает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

= НЕ (логическое_значение). Принимает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и наоборот.

Функции даты и времени:

= ДАТА (год; месяц; день) — возвращает десятичное значение даты, которое представляет собой количество дней между базовой и заданной датой.

= СЕГОДНЯ () — возвращает десятичное значение текущей даты.

= ВРЕМЯ (часы; минуты; секунды) — возвращает десятичное значение, которое представляет собой долю суток между полуночью и заданным временем. Аргумент секунды является необязательным: его можно опустить, но точку с запятой после аргумента минуты все равно надо поставить.

Статистические функции:

= СРЗНАЧ (число1; число2;…) — вычисляет среднее арифметическое значение. Функция игнорирует пустые, логические и текстовые ячейки.

= МАКС (число1; число2;…), = МИН (число1; число2;…) — возвращают наибольшее и наименьшее значения из набора данных.

= СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) — каждая ячейка диапазона проверяется на соответствие критерию, прежде чем прибавляется к итогу.

= СЧЕТЕСЛИ (диапазон; критерий) — вычисляется количество ячеек, которые удовлетворяют заданному критерию.