Формулы и функции

Операции с ячейками, ссылки

Электронная таблица и модель ячейки

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

Модель ячейки в ЭТ имеет пять следующих уровней. Изображение на экране – это отформатированные значения - числа, текст, результат вычисления формул. Скрытое форматирование – это форматы чисел (показаны ниже на рис. 25), тип шрифта, вид рамки, уровень защиты ячейки. Формула, которая состоит из чисел, значков и математических выражений. Имя ячейки, однозначно идентифицирующее каждую конкретную ячейку. Комментарий (примечание) - это текст, который через дополнительное окно связывается с ячейкой. При этом в правом верхнем углу ячейки показывается красная точка. Зеленая точка в левом верхнем углу появится при некоторых проблемах с использованием формул (по итогам их аудита).

Программа MS Excel 2010 является одной из наиболее распростаненной в мире для работы с ЭТ на персональных компьютерах. Она имеет все самые современные компоненты и режимы, работает как самостоятельная программа или в составе пакета MS Office 2010.

С помощью Excel можно создавать и форматировать электронные книги (наборы листов Excel) для анализа данных и принятия деловых решений[1]. Например, с помощью Excel можно отслеживать данные, разрабатывать модели анализа данных, применять формулы для вычислений с этими данными, сводить данные множеством способов, а также отображать их на профессионально выглядящих диаграммах различных видов. Среди областей применения Excel следующие: 1) Учет (работа в Excel с различными финансовыми документами, отчетами о движении денежных средств, отчетами о доходах или отчетами о прибылях и убытках). 2) Разработка бюджета (личного или для компании, для мероприятия и т.д.). 3) Подготовка счетов и накладных (Excel можно применять для управления данными о продажах, создавать нужные формы, счета и накладные). 4) Создание отчетов различных типов, в которых анализируются или обобщаются данные о ходе выполнения проекта, о расхождениях между предполагаемыми и фактическими результатами, выполняется прогнозирование данных. 5) Планирование (создание профессиональных планов типа расписание занятий на неделю, план маркетинговых исследований, план налогообложения, планы отпусков). 6) Отслеживание (с помощью Excel можно отслеживать данные в листах учета рабочего времени, либо товарных списках для складского учета). 7) Работа с календарями (Excel позволяет создавать календари различных видов — например, учебный календарь для отслеживания различных видов деятельности в течение учебного года, либо календарь финансового года для отслеживания бизнес-событий и этапов) и т.д. и т.п.

Среди основных задач Excel:

1) Создание книги (Книгу можно создать на основе шаблона пустой книги или другого существующего шаблона, уже содержащего некоторые данные, макет и форматирование. Для этого нужно открыть вкладку Файл. Откроется Представление Microsoft Office Backstage. Нажмите кнопку Создать. В области Доступные шаблоны выберите нужный шаблон книги).

2) Ввод данных на листе (Щелкните ячейку и введите в нее данные. Нажмите клавишу ВВОД или TAB, чтобы перейти к следующей ячейке. Чтобы ввести набор данных, например дни, месяцы или порядковые номера, введите в ячейку начальное значение, а затем в следующую ячейку введите значение, определяющее шаблон. Выделите ячейки с начальными значениями и перетащите маркер заполнения (Небольшой черный квадрат в правом нижнем углу выделенного блока. При наведении на маркер заполнения указатель принимает вид черного креста) по диапазону, который необходимо заполнить).

3) Настройка параметров (Чтобы ширина столбцов и высота строк автоматически изменялись в соответствии с содержимым ячейки, выделите столбцы или строки, которые необходимо изменить, а затем на вкладке Главная в группе Ячейки нажмите кнопку Формат. В меню Размер ячейки выберите команду Автоподбор ширины столбца или Автоподбор высоты строки).

4) Форматирование данных (Чтобы применить форматирование к числам, выделите нужную ячейку с числами, а затем на вкладке Главная в группе Число щелкните стрелку рядом со списком Общий и выберите нужный формат (рис. 26).

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

В справочной системе Microsoft Excel 2010 [2] детально описаны методики выполнения следующих задач: форматирование чисел на листе, печать листа, создание таблицы Excel, фильтрация данных с помощью автофильтра, сортировка данных с помощью автофильтра, применение условного форматирования, применение проверки данных, создание формулы, использование функций в формулах, отображение данных на диаграмме, создание макроса, создание отчета сводной таблицы, активация и использование надстроек.

Рис. 25. Форматирование ячеек

2. Интерфейс программы M S Excel 2010

Интерфейс программы MS Excel 2010 показан на рис. 26. Элементами интерфейса являются: строка заголовка, строка меню, панель инструментов, панель форматирования, поле имени ячейки, строка формул, заголовок столбца и заголовок строки, ярлычки листов, полосы прокрутки и строка состояния.

Рис. 26. Интерфейс программы MS Excel 2010

Файл MS Excel 2010 называется рабочей книгой. Она состоит из листов, имена которых (Лист1, Лист2, … ) выведены на ярлыках в нижней части окна рабочей книги. Количество столбцов и строк практически не ограничено. Заголовок столбца обозначается буквами A, B, C... Z, AA, AB,... AZ..., а заголовок строки нумеруется 1, 2, 3 и т.д. Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги. Имеется д ва режима просмотра для работы(меню Вид):

· Обычный – наиболее удобный для выполнения большинства операций.

· Разметка страниц – удобен для окончательного форматирования таблицы перед печатью. Границы между страницами в этом режиме отображаются синими пунктирными линиями. Границы таблицы – сплошной синей линией.

Настройка содержимого и вида панелей инструментов MS Excel 2010 выполняется через меню.

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

CTRL+INS - скопировать объект в буфер.

SHIFT+DEL - вырезать объект в буфер.

SHIFT+INS - вставить объект из буфера в таблицу.

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

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

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

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

Правая кнопка мышки над выделенным объектом ЭТ дает контекстно-зависимое меню (вырезать, копировать, вставить, удалить, очистить, форматировать). Специальная вставка позволит для объекта работать по выбору - вставить все или только формулы, значения, форматы, примечания.

Важнейший этап работы с ЭТ - это оформление листов. При этом можно выбирать через меню (формат/ячейки) по закладкам - формат чисел, выравнивание, шрифт, размер, вид, стиль, защита (см. рис 5). Рекомендуется соблюдать следующие правила - не более 2-х, 3-х типов и 2-х размеров штифтов на 1 странице, не увлекаться при назначении стилей (жирно, курсив, подчеркивание) и при выборе цвета и узоров, чтобы не перегружать таблицу.

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

Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца. Например, если ячейка находится в столбце F и строке 7, то она имеет адрес F7.

Ссылкой однозначно определяется ячейка или группа ячеек листа, а также упрощается поиск значений или данных, используемых в формуле. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками. По умолчанию в MS Excel 2010 используются ссылки A1, в которых столбцы обозначаются буквами от A до Z и т.д., а строки числами. Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой.

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

Чтобы сослаться на:	Введите:
Ячейку столбца A и 10-й строки	A10
Диапазон ячеек столбца A с 10-й строки по 20-ю	A10:A20
Диапазон ячеек в 15-й строке со столбца B по столбец E	B15:E15
Все ячейки в 5-й строке	5:5
Все ячейки между 5-й и 10-й строками включительно	5:10
Все ячейки в столбце H	H:H
Все ячейки между столбцами H и J включительно	H:J

Можно воспользоваться стилем, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый R1C1, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы «R» указывается номер строки ячейки, после буквы «C» — номер столбца.

В зависимости от поставленной задачи можно использовать относительные ссылки, которые ссылаются на ячейки относительно позиции формулы, или абсолютные ссылки, которые ссылаются всегда на одну и ту же ячейку. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками. Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере: =A5*$C$1

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

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

Расчеты начинается с вставки и редактирования формул. Используется такая технология. Выберите ячейку для формулы, например, С8. Знак =. Щелчок мышкой на С5, затем +, щелчок на С7. Окончание ввода формулы по клавише Enter. Вычисление содержимого ячейки - в строке формул это записи типа =С5+С6+С7 (здесь знак равенства означает, что далее записана формула) или типа =СУММ(D4:D7), что в обоих случаях означает суммирование содержимого ячеек из диапазона. Примеры.

=5+2*3 Добавляет 5 к произведению 2 и 3.

=СЕГОДНЯ() Возвращает текущую дату.

=IF(A1>0;"Плюс";"Минус") Проверяет, содержит ли ячейка A1 значение больше 0. Если результат проверки положителен, в ячейке появляется слово "Плюс", в противном случае — слово "Минус".

Функции (это запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений) включаются либо через пиктограмму "Сумматор" либо через "Интеграл" и тогда запускается диалог с "Мастером функций" (см. рис. 27 и 28).

Рис. 27. Мастер функций MS Excel 2010 (шаг 1 из 2)

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

Рис. 28. Мастер функций MS Excel 2010 (шаг 2 из 2)

Библиотека функций программы MS Excel 2010 [8] насчитывает около 330 различных функций, объединенных в следующие тематические группы: функции баз данных, функции даты и времени, инженерные функции, финансовые функции, информационные функции, логические функции, функции ссылки и автоподстановки, арифметические и тригонометрические функции, статистические функции, текстовые функции.