Основы работы с электронными таблицами. Назначение, основные возможности
Приложение Excel — это электронная таблица. Данные в этой таблице разбиты по ячейкам. Данные могут быть как текстовыми, так и числовыми. Для обработки данных в Excel предлагается широкий набор встроенных функций. Важно то, что значения в ячейках могут рассчитываться на основе значений других ячеек. Все это обеспечивает неизменный успех и популярность Excel на рынке офисных программных продуктов.
Интерфейс Microsoft Excel
После запуска приложения Excel открывается типичное для программ Microsoft Office рабочее окно. Главная особенность окна Excel состоит в том, что центральная рабочая область разграфлена в виде сетки и представляет собой массив ячеек. Именно в эти ячейки вводятся данные.
Лента размещена в верхней части окна приложения и содержит несколько вкладок с пиктограммами всевозможных команд.
Вкладка Файл
Предназначена для выполнения операций уровня работы с файлом. Содержит пиктограммы Сведения, Создать, Открыть, Сохранить, Сохранить как, Печать, Общий доступ, Экспорт, Опубликовать, Закрыть, учетная запись, Параметры.
Пиктограмма Сведения позволяет получить информацию о файле: размер, название, писковые теги, даты создания, модификации и т.д. Также можно настрить защиту рабочей книги, проверить соответствие версии Excel, контролировать версии файла.
Для открытия, закрытия и сохранения документа на вкладке Файл предназначены соответственно пиктограммы Открыть, Закрыть и Сохранить. Также для сохранения документа может использоваться пиктограмма Сохранить как. В этом случае можно выбрать формат сохраняемого файла. С помощью пиктограмм раздела можно создавать как пустые рабочие книги, так и документы на основе шаблонов и уже существующих книг. В частности, для создания новой пустой книги выбираем пиктограмму Пустая книга. В результате создается новая книга.
Пиктограмма Печать вызывает диалог настройки печати файла.
Работа с ячейками
Мало ввести в ту или иную ячейку значение — необходимо как-то различать эти ячейки. Наиболее оптимальный способ добиться этого состоит в адресации. Адресация ячеек подразумевает, что каждая ячейка в рабочем документе имеет свой уникальный адрес. В частности, в Excel традиционно используется принцип адресации, который состоит в том, что для ячейки указывается строка и столбец, на пересечении которых находится ячейка. По умолчанию используется адресация типа A1, когда строки нумеруются последовательностью натуральных чисел, а столбцы — латинскими буквами. Ячеек в рабочей области очень много — последняя строка имеет номер 1048576, а последний столбец имеет название XFD (всего 16384 столбца).
Чтобы однозначно определить ячейку, необходимо и достаточно указать ее адрес, который традиционно формируется объединением имени столбца и номера строки.
Для совместимости с пакетом Lotus 1-2-3, долгое время являвшемся эталоном электронных таблиц, но в настоящее время не поддерживающимся, возможна адресация типа R1C1, когда после R указывается номер строки, а после C — номер строки. В ряде случаев это удобно, например, при смещении на заданное число столбцов влево или вправо гораздо проще добавить нужное число к номеру столбца, чем “вычислять” буквенное имя нужного столбца. Это используется самим пакетом Excel при автоматическом формировании макросов.
Для удобства пользователя, опять же, по умолчанию, номера строк отображаются в вертикальной полосе вдоль левой границы рабочей области (полоса заголовков строк), а названия столбцов (полоса заголовков столбцов) — в горизонтальной полосе в верхней части рабочей области.
На самом деле рабочее окно приложения Excel содержит не одну, а несколько рабочих областей, которые называются листами. В нижней части рабочего окна приложения находятся вкладки (корешки) листов рабочего документа Excel. Сам рабочий документ называется книгой (или рабочей книгой). Чтобы выделить тот или иной рабочий лист, достаточно щелкнуть по корешку этого листа.
Рабочие листы не являются обособленными компонентами окна приложения. Между ячейками листов можно задавать функциональные зависимости. Но прежде, чем рассматривать методы работы с несколькими листами в одной книге имеет смысл обсудить базовые операции, которые выполняются с ячейками и диапазонами ячеек в области одного листа.
По большому счету, при работе непосредственно с ячейками рабочего документа речь может идти о вводе данных или об их редактировании. Сюда же относится и форматирование уже введенных в документ данных — применение специального способа отображения содержимого в ячейке (отличного от того, что используется по умолчанию). Принципиальное значение имеет тип данных, которые введены в ячейку документа. Условно данные можно разделить на три группы:
текст;
числа;
формулы.
В ячейке рабочей области значение может быть определено пользователем непосредственно при вводе (это числа и текст) или вычислено на основе значений в других ячейках. В последнем случае по определенным правилам в ячейку вводится формула, которая, как правило, содержит ссылки на другие ячейки (адреса этих ячеек). Если значения ячеек, на которые ссылается ячейка с формулой, меняются, автоматически пересчитывается и формула (во всяком случае, такой режим используется по умолчанию). При этом ячейки с формулами могут содержать ссылки на ячейки как с числами и текстом, так и с формулами.
Ввод данных
Существуют несколько способов ввода данных в ячейки: непосредственно в выделенную ячейку или в строку формул, а также импорт данных из других файлов. Именно совместимость форматов электронных таблиц и баз данных позволяет,во-первых, существенно упростить процедуру переноса данных между приложениями, во-вторых, использовать совместимые адресации типа R1C1, в-третьих, использовать “скрытые” функции, примером которых является рассматриваемая далее функция РАЗНДАТ, взятая из Lotus 1-2-3 и неописываемая в документации по Microsoft Excel.
Рассмотрим подробнее первые два способа ввода.
И в том, и в другом случае предварительно выделяется ячейка, в которую вводится значение. Для выделения на ячейке выполняют щелчок мышью либо перемещают рамку выделения ячейки в нужное место рабочего документа с помощью клавиш со стрелками. Также можно воспользоваться полем названий (белое поле с адресом ячейки в левом верхнем углу рабочей области документа — слева от строки формул).
В поле названия можно вести адрес ячейки и нажать клавишу <Enter>, в результате чего эта ячейка выделится (данный способ выделения ячеек удобен при работе с большими документами). Далее осуществляется ввод значения в ячейку. При этом автоматически выполняется переход в режим ввода значения в ячейку: в области выделенной ячейки мигает курсор ввода.
В строке формул при вводе значения в ячейку также отображается это значение. Слева от строки формул в поле названия отображается имя (адрес) активной на данный момент ячейки. Для завершения ввода достаточно нажать клавишу <Enter>, клавишу со стрелкой или щелкнуть мышью на другой ячейке.
Для ввода значения через строку формул сначала выделяют правильную ячейку, после чего выполняют щелчок мышью в строке формул и выполняют ввод значения.
Если просто выделить ячейку и начать ввод, то старое значение ячейки автоматически удаляется. Это не всегда удобно. Для редактирования, а не замены значения ячейки, ячейка выделяется, после чего нажимают клавишу <F2>. Можно также выполнить двойной щелчок мышью на редактируемой ячейке. Еще один способ редактирования ячейки — выделить ее и перейти к строке формул.
Диапазоны ячеек
Операции в рабочем документе можно выполнять не только с отдельными ячейками, но и с блоками ячеек, т. е. несколькими ячейками одновременно. Назовем, как это принято в Excel, выделенный блок ячеек диапазоном.Общее правило состоит в том, что для выполнения тех или иных действий с диапазоном ячеек этот диапазон в рабочей области необходимо как-то выделить.
Диапазон можно выбрать несколькими способами.
- Щелкните левой кнопкой мыши и перетащите ее указатель по диапазону ячеек. Если перетащить указатель за пределы экрана, то рабочая таблица будет автоматически прокручиваться.
Нажмите и удерживайте клавишу <Shift>, а затем выделите диапазон с помощью клавиш управления курсором.
Для входа в режим расширения области выделения нажмите клавишу <F8> (в строке состояния появится сообщение Расширить выделенный фрагмент), а затем с помощью клавиш управления курсором переместите табличный курсор, выделяя диапазон. Чтобы вернуть клавиши управления курсором в нормальный режим, снова нажмите клавишу <F8>.
Введите адрес ячейки или диапазона ячеек в поле Имя и нажмите клавишу <Enter>. Excel выделит указанную ячейку или диапазон ячеек.
Выберите команду Главная|Редактирование|Найти и выделить|Перейти (или нажмите клавишу <F5>) и введите вручную адрес диапазона в диалоговом окне Переход. После щелчка на кнопке ОК программа выделит ячейки указанного диапазона.
Отметим, что при выделении диапазона ячеек среди всех ячеек диапазона имеется одна активная. Она выделена в диапазоне ячеек белым цветом. В строке названий отображается ее адрес. Если нажать клавишу <Enter>, активной станет следующая ячейка, расположенная снизу под текущей. Последовательно нажимая клавишу <Enter>, можно перебрать все ячейки диапазона. Ячейки перебираются по столбикам: после нижней ячейки в столбике активной становится верхняя ячейка следующего столбика. В таком режиме можно заполнять диапазон ячеек значениями.
Можно выделить сразу несколько диапазонов. Для этого при выделении каждого следующего диапазона необходимо держать нажатой клавишу <Ctrl>.
Если во все ячейки диапазона необходимо ввести одинаковые значения, достаточно в активную ячейку диапазона ввести это значение и нажать комбинацию клавиш <Ctrl>+<Enter>.
Нередко возникает необходимость выделить всю строку или столбец. Для выделения строки наводим курсор мыши на ленту нумерации строк (в соответствующей позиции с номером выделяемой строки). Курсор мыши при этом приобретает вид стрелки, направленной вдоль строки. Щелчок левой кнопкой мыши приводит к выделению строки. Так же выделяются столбцы, с той лишь разницей, что курсор мыши наводится на ленту именования столбцов. Наконец, чтобы выделить весь рабочий лист, наводим курсор в область пересечения лент именования столбцов и индексации строк и нажимаем левую кнопку мыши.
Выделение ячеек определенного типа
Excel предоставляет также возможность выбора в рабочей книге или в выделенном диапазоне ячеек особых типов. Например, часто возникает необходимость выделить ячейки, содержащие формулы, либо ячейки, от содержимого которых зависит формула в данной ячейке. Excel предоставляет простое средство локализовать такие и подобные им ячейки. Для этого выберите команду Главная | Редактирование |Найти и выделить |Выделить группу ячеек — и откроется диалоговое окно Выделить группу ячеек.
После того как вы выберете в этом диалоговом окне нужную опцию, Excel выделит соответствующее подмножество ячеек в диапазоне, выбранном в текущий момент. Как правило, в результате выделяется несмежный диапазон. Если же не будет найдено ни одной подходящей ячейки, программа сообщит вам об этом.
Формулы и функции Excel
Формулы — это то, что делает электронные таблицы такими полезными. Электронная таблица без формул, по сути, превращается своеобразный документ некоего текстового процессора, котором реализована мощная поддержка представления информации виде таблиц. Именно формулы позволяют вычислять результаты на основе данных, которые хранятся ячейках рабочего листа. Если вам понадобится изменить какие-либо данные, то все результаты вычислений будут автоматически пересчитаны. В Excel формула представляет собой специальный код, введенный ячейку. Формула предполагает выполнение вычислений определенного типа, результат которых и отображается данной ячейке.
Формула, введенная ячейку, всегда начинается со знака равенства и может со держать любые из следующих элементов.
• Математические операторы).
• Ссылки на ячейки (включая именованные ячейки и диапазоны).
• Числовые значения или текст.
• Функции рабочего листа.
Если ввести формулу ячейку, то ней отобразится результат расчета по этой формуле, сама формула при выборе этой ячейки будет отображаться строке формул.
Операторы
Оператор — это специальный символ, обозначающий тип математической (или логической) операции, выполняемой формулах. Операторы делятся по форме записи на унарные (один операнд после оператора) и бинарные (2 операнда по обе стороны оператора).
| Оператор | Назначение |
|---|---|
| + бинарный | Сложение |
| + унарный | Сохранение знака чили начало формулы (в последнем случае автоматически заменяется на =+) |
| - бинарный | Вычитание |
| - унарный | Инверсия знака или начало формулы (в последнем случае автоматически заменяется на =-) |
| * | Умножение |
| / | Деление |
| ^ | Возведение в степень |
| & | Объединение строк |
| = (второе и далее) | Логическое сравнение “равно” |
| > | Логическое сравнение “больше” |
| < | Логическое сравнение “меньше” |
| >= | Логическое сравнение “больше или равно” |
| <= | Логическое сравнение “меньше или равно” |
| <> | Логическое сравнение “не равно” |
Результатом логического сравнения является одно из значений “ИСТИНА” или “ЛОЖЬ”.
Порядок выполнения операторов определяется таблицей
| Оператор | Действие | Приоритет |
|---|---|---|
| ^ | Возведение степень | 1 |
| * | Умножение | 2 |
| / | Деление | 2 |
| + | Сложение | 3 |
| - | Вычитание | 3 |
| & | Соединение строк | 4 |
| = | Равно | 5 |
| < | Меньше чем | 5 |
| > | Больше чем | 5 |
Из этой таблицы видно, что оператор возведения степень имеет самый высокий приоритет (т.е. выполняется первым), оператор логического сравнения — самый низкий (выполняется последним). Используя формулах круглые скобки, можно изменить порядок выполнения операторов, поскольку сначала всегда вычисляются выражения, заключенные в круглые скобки.В формулах можно использовать и так называемые вложенные скобки, т.е. скобки, заключенные другие скобки. Если формуле есть вложенные скобки, то Excel вычисляет сначала выражения, которые находятся самых “внутренних” скобках, затем движется изнутри наружу.
Использование функций
При создании формул можно использовать встроенные функции Excel.
Вызов функции в формуле состоит из названия функции и списка аргументов в круглых скобках. Функции различаются по тому, как эти аргументы них используются. В зависимости от этого определяются следующие типы функций.
• Функции без аргументов
• Функции одним аргументом
• Функции фиксированным числом аргументов
• Функции переменным числом аргументов
• Функции с необязательными аргументами
Если функции используется несколько аргументов, то каждый из них отделяется точкой запятой.
В качестве аргумента может выступать адрес ячейки (любой упомянутый выше тип) или диапазон, числовое значение, текстовая строка, выражение или другая функция.
В Excel насчитывается более 400 различных встроенных функций, группирующихся по назначению. Рассмотрим некоторые из часто используемых функций.
Математические функции
Функции данной группы предназначены для использования в расчетах. Аргументами являются числа, адреса ячеек или диапазонов, возвращаемыми значениями - числа.
Примеры часто используемых математических функций:
LN(X) - натуральный логарифм аргумента;
LOG10(X) - десятичный логарифм аргумента;
SIN(X) - синус аргумента;
COS(X) - косинус аргумента;
TAN(X) - тангенс аргумента;
EXP(X) - экспонента (значение \(e^X\) ) ;
ОСТАТ(X;N) - остаток от целочисленного деления первого аргумента на второй.
Текстовые функции
Как правило, работа в Excel предполагает не только выполнение вычислений, но и преобразование или необходимую подготовку различной информации для помещения в базу данных. И очень часто эти действия предусматривают различные манипуляции текстовыми строками.
Когда вы вводите в ячейку данные, Excel сразу же определяет, что именно вы вводите: формулу, число (включая дату и время) или что-нибудь еще. Вот это “что-нибудь еще” и воспринимается программой как текст.
Действие большинства текстовых функций не ограничено только текстовыми данными. Как правило, они также могут работать с ячейками, содержащими числовые значения.
Функция ОБЪЕДИНИТЬ позволяет реализовать сложные объединения текстовых строк. Она имеет несколько аргументов.
Первый аргумент определяет разделитель, который будет вставлен между содержимым объединяемых ячеек. Если в качестве его значения задать запятую, то при объединении текстовых значений обрабатываемых ячеек программа вставит между ними этот символ.
Второй аргумент указывает, как следует поступить с пустыми ячейками. Он может принимать только два значения — истина или ложь. В первом случае программа будет просто игнорировать пустые ячейки. Фактически этот аргумент определяет, как в результирующей строке будут расставлены разделители, определенные в пером аргументе. Если значение второго аргумента — ИСТИНА, то программа не будет вставлять дополнительные разделители (например, запятые) при обнаружении пустых ячеек в обрабатываемом диапазоне.
Третий аргумент определяет текст, который объединяется. Это может быть просто текстовая строка или массив строк, скажем, диапазон ячеек. Функция ОБЪЕДИНИТЬ требует, чтобы в этом аргументе было задано не менее одного значения или ссылки на ячейку.
В Excel предусмотрены три функции, изменяющие регистр символов текста — в верхний, нижний или смешанный регистр (первая буква каждого слова будет в верхнем регистре, а остальные — в нижнем). Эти функции требуют передать им лишь указатель на тот текст, который нужно обработать. Как можно догадаться, функция ПРОПИСИ переводит все символы в ВЕРХНИЙ РЕГИСТР (т.е. в прописные буквы), функция СТРОЧН — в нижний регистр (т.е. в строчные буквы), а функция ПРОПНАЧ устанавливает первые буквы всех слов прописными, а остальные — строчными. Полезно будет уточнить, что эти функции оперируют только буквами; другие символы они просто игнорируют, возвращая их без изменения.
Функция ЛЕВСИМВ возвращает указанное количество символов с начала (левой стороны) текстовой строки. У этой функции два аргумента: текстовая строка, из которой извлекается фрагмент, и количество символов, определяющее длину этого фрагмента.
Функция ПРАВСИМВ возвращает указанное количество символов начиная с конца (правой стороны) текстовой строки. У этой функции два аргумента: текстовая строка, из которой извлекается фрагмент, и количество символов, определяющее длину этого фрагмента.
Функция ПСТР возвращает указанное число символов из середины текстовой строки, начиная с заданной позиции. У этой функции три аргумента: текстовая строка, из которой извлекается фрагмент, номер символа в строке, с которого начинается извлекаемый фрагмент, и количество символов, определяющее длину этого фрагмента.
Функция ТЕКСТ имеет два аргумента: числовое значение и допустимый в Excel код форматирования. К числовому значению можно применить любое форматирование, допустимое в том смысле, что программа Excel сможет его распознать