Теоретические сведения

Теоретические сведения

Основные понятия. Электронные таблицы — инструмент для автоматизированной обработки табличной информации на ЭВМ. Документ, который создается электронными таблицами Excel, называется Книга. Она по умолчанию содержит три Листа (для работы можно создать любое количество листов), на каждом из которых можно создать таблицы, диаграммы, текстовую информацию и т. д. Рабочая область Листа представляет собой табличную структуру, состоящую из прямоугольных клеток — ячеек. Горизонтальные ряды клеток образуют строки, вертикальные ряды — столбцы. Каждая ячейка имеет свой адрес, состоящий из имени столбца и номера строки, к которым она принадлежит. Примеры адресов: A1, F34, M245, CA123 и т. д. Ячейка, в которой находится указатель, (она обрамлена рамкой) является активной.

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

Адрес блока — координаты противоположных углов, разделенных двоеточием. Например, A1:C20.

Содержимым ячейки электронной таблицы может быть текст, число или формула. Текст — это последовательность любых символов. При вводе чисел целые и дробные части разделяются запятой или точкой в зависимости от настройки. Если при вводе числа в ячейку его длина превышает ширину ячейки, то оно отобразится в экспоненциальной форме (например, 2,13Е + 08, что означает число 213000000 или 2,13*108) или вместо числа появятся символы ####. Число в ячейке можно представить в разных форматах, например, целое, вещественное, дата, время, денежный формат и др.

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

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

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

При работе с формулами возможно использование двух типов ссылок: относительных и абсолютных. Различия в них проявляются при копировании формул. Относительная ссылка используется для указания адреса ячейки, вычисляемого относительно ячейки, в которой находится формула. При перемещении и копировании формулы относительные ссылки автоматически обновляются в зависимости от нового положения формулы. Относительные ссылки имеют следующий вид: A1; F23 и т. д.

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

Если $ стоит перед числом (A$1), то наоборот. Такие ссылки называются смешанными.

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

1. Настройка параметров рабочего Листа. Настройка параметров Листа в MS Excel 2010 осуществляется на панели Разметка страницы, где можно установить Темы, Параметры страницы, Параметры листа и др.

2. Создание заголовка таблицы. Заголовок таблицы (“шапка”) обычно отражает тип данных в соответствующем столбце.

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

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

Далее необходимо выделить весь заголовок (“шапку”) таблицы (ячейки от А3 до G3) и произвести форматирование ячеек (Главная, группа Число → Формат ячеек).

В открывшемся интерактивном окне активизировать вкладку Выравнивание и установить параметры (например, Выравнивание: по горизонтали → по центру, по вертикали → по центру, Отображение → переносить по словам). Для обрамления заголовка (“шапки”) активизировать вкладку Граница, выбрать тип и цвет линии, указать стороны для обрамления.

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

Многоуровневая “шапка” таблицы создается путем объединения группы смежных ячеек (выделить группу ячеек и активизировать команду Объединить и поместить в центр на панели Главная, группа Выравнивание).

3. Операция копирования. Большое значение при работе с электронными таблицами имеет операция копирования констант, переменных и особенно формул.

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

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

Копирование текстовой константы. Установить указатель в копируемую ячейку и выполнить операцию копирования, как с числовой константой. Если текстовая константа заканчивается числом (например, Методика 1), то при копировании в смежные ячейки число будет увеличиваться на единицу. Если при копировании удерживать клавишу Ctrl, то число увеличиваться не будет.

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

Например, составим таблицу умножения для числа 8. Для этого в ячейку А1 введем выражение “Таблица умножения на число”, в ячейку D1 — число 8, в А2–А10 — цифры с 1 до 9 (можно ввести цифру 1 и воспользоваться операцией копирования числовой константы совместно с клавишей Ctrl), в столбец В — знак умножения (можно воспользоваться операцией копирования текстовой константы), в столбец С — цифру 8, в D — знак =.

Для ввода формулы в ячейку Е2 необходимо установить указатель в эту ячейку, набрать =, щелкнуть левой клавишей мыши в ячейку А2, набрать знак *, щелкнуть в ячейку С2. После нажатия на клавишу Enter в ячейке Е2 отобразится результат. Формулу также можно набрать полностью с клавиатуры (=А2*С2, где А и С — буквы английского алфавита).

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

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

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

Для автоматизации данной задачи в электронных таблицах необходимо: заполнить одну строку (например, строку 1) числами от 1 до 9 (ввести в ячейку В1 число 1 и воспользоваться процедурой копирования числовой константы, удерживая клавишу Ctrl). Аналогично заполнить столбец А, начиная с адреса А2 числами от 1 до 9. В ячейку В2 ввести формулу (=$A2*B$1), которая при копировании фиксирует строку 1 и столбец А. Скопировать формулу вправо, затем вниз.

4. Работа с функциями. Функции облегчают обработку табличной информации при их использовании в формулах.

В Microsoft Excel имеется огромная библиотека функций, классифицированная по категориям (панель Формулы, группа Библиотека функций). Для получения всего перечня функций и их описаний необходимо обратиться в справку Microsoft Excel, выбрать пункт Справка по функции — Список функций листа (по категориям) или Список функций листа (по алфавиту).

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

Воспользуемся статистической функцией СЧЁТЕСЛИ (Диапазон; Критерий), которая подсчитывает количество ячеек в диапазоне по заданному пользователем критерию (Формулы → группа Библиотека функций → Другие функции → Статистические → СЧЁТЕСЛИ()).

В открывшемся диалоговом окне Аргументы функции указать диапазон ячеек (указывается выделением области ячеек с помощью мыши или вводится с клавиатуры) и критерий подсчета (так, для подсчета количества отличных оценок конечная формула будет выглядеть =СЧЁТЕСЛИ(C10:C29;5), где С10:С29 — диапазон ячеек, содержащих оценки по русскому языку, 5 — критерий счета). Аналогично можно подсчитать количество хороших, удовлетворительных и неудовлетворительных оценок. Формула для подсчета количества учеников, неявившихся на экзамен, будет выглядеть следующим образом: =СЧЁТЕСЛИ(C10:C29;“неявка”).

5. Работа с диаграммами. Microsoft Excel предоставляет большие возможности наглядного представления информации с помощью графиков и диаграмм (Вставка → группа Диаграммы). Любая диаграмма создается на основе предварительно созданных исходных данных. Например, для вывода графика функции y = x2 в интервале [–5, 5] с шагом 0,5 необходимо выполнить следующие действия:

— в столбец, предназначенный для исходных данных, ввести значения х в интервале [–5, 5] с шагом 0,5 (для упрощения можно ввести два значения из ряда, выделить обе ячейки и воспользоваться операцией копирования);

— в столбец для вычисления х2 по правилам MS Excel ввести формулу вычисления и, воспользовавшись операцией копирования, распространить формулу для всех значений;

— выделить всю область с данными (в нашем случае А1:В22) и построить график функции (Вставка → группа Диаграммы → Точечная → Точечная с гладкими кривыми).

К полученному графику можно применить различные стили, цвета, макеты и т. д.

Аналогично строятся диаграммы для наглядного представления различных данных, в том числе и учебного процесса. Например, для наглядного представления средних значений оценок по предметам в сводной ведомости необходимо выделить области для построения диаграммы (выделить “шапку” таблицы от В4 до Н4; удерживая клавишу Ctrl, выделить средние оценки (ячейки от В25 до Н25)) и построить диаграмму (Вставка →→ Гистограмма → Гистограмма с группировкой).

6. Сортировка и фильтрация данных. Сортировка и фильтрация данных в MS Excel осуществляется при помощи панели Данные, группа Сортировка и фильтр. При активизации команды сортировки, в появившемся интерактивном окне “Сортировка” необходимо установить соответствующие параметры.

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

При этом в заголовке (“шапке”) таблицы в каждом столбце появится кнопка выбора критерия.

Для установки критериев отбора данных таблицы нужно отмечать элементы для вывода.

Пункт Числовые фильтры позволяет установить более сложные фильтры отбора данных. Например, необходимо вывести на экран фамилии всех учеников, у которых оценка по русскому языку выше среднего и оценка по литературе равна 5 (выбрать кнопку фильтрации оценок по русскому языку → Числовые фильтры → Выше среднего, выбрать кнопку фильтрации оценок по литературе → Числовые фильтры → равно → 5).

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