Большие массивы числовых данных обрабатываются с помощью электронных таблиц.

Электронная таблица - эта работающая в диалоговом режиме программа обработки числовых данных в табличном виде.

В дальнейшем будем рассматривать электронные таблицы (ЭТ) на примере MS Excel.

Структура электронных таблиц.

ЭТ состоит из столбцов и строк. Заголовки столбцов обозначаются буквами или сочетаниями букв. (А, В, АС), заголовки строк - числами (1, 16, 278 и т.д.). Ячейка - место пересечения строки и столбца, имеющее свой адрес (А1, С10). Ячейка, с которой производятся какие-либо действия, выделяется рамкой и называется активной. Ячейка является "минимальным элементом" электронной таблицы. В правом нижнем углу активной ячейки есть маленький черный квадратик, называемый маркером заполнения. При наведении на него мыши он превращается в тонкий крестик.

Рабочий лист состоит из множества ячеек. Из рабочих листов состоит рабочая книга - файл Excel . По умолчанию листы именуются Лист 1, Лист 2, Лист 5 и т.д. Имена и порядок расположения листов можно изменять с помощью выбора соответствующего пункта контекстного меню. Можно выполнять вычисления, выбирая данные из нескольких листов. В левой нижней части окна размещены ярлычки листов и кнопки прокрутки.

Подобно другим приложениям Windows, окно программы Excel содержит заголовок окна, строку горизонтального меню, панели инструментов "Стандартная" и "Форматирование".

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

Данные в ЭТ заносятся непосредственно в ячейку и дублируются в строке формул, которая расположена под п.и. "Форматирование". Перемещение между ячейками осуществляется с помощью стрелок, клавиши табуляции или с помощью мыши. Зафиксировать данные в ячейке можно с помощью нажатия клавиши , либо с помощью мыши.

Выделение фрагментов ЭТ.

Элемент Действия
Ячейка Щелкнуть мышью или нажать клавиши со стрелками.
Блок ячеек Переместить мышь, удерживая левую кнопку от первой ячейки к последней.
Несмежные ячейки Выделить 1-ю ячейку и при нажатой клавише "CTRL", выделить остальные ячейки.
Строка Щелкнуть по заголовку строки.
Столбец Щелкнуть по заголовку столбца.
Вся таблица Нажать кнопку на пересечении столбцов и строк.

Формат и типы данных.

Выделяются три основных типа данных: число, текст и формула.

Команда Формат-Ячейки позволяет выбрать нужный формат данных.

Числовой формат используют для представления чисел. По умолчанию отображаются два десятичных знака после запятой и числа выравниваются по правому краю для удобства представления разрядов. Числа, если они очень большие, могут быть представлены в экспоненциальном формате, например, 2,00Е+0,09. Может быть специализированный формат, например, Дата-Время, Денежный.

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

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

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

Иногда для размещения данных в ячейках необходимо изменить их ширину или высоту. Это можно сделать вручную с помощью перетаскивания границ ячеек в поле адресации или с помощью команды Формат-Строка/Столбец-Высота.../Ширина... Данные должны полностью помещаться в ячейку, в противном случае выводятся символы ####.

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

Относительные и абсолютные ссылки.

Относительная ссылка в формуле используется для указания адреса ячейки, вычисляемого относительно ячейки, в которой находится формула. При перемещении или копировании формулы из активной ячейки относительные ссылки автоматически обновляются в зависимости от нового положения формулы. Именно такие ссылки используются в ЭТ по умолчанию, например, когда нужно умножить все числа в одном столбце на все соответствующие числа в другом столбце. Относительные ссылки имеют вид: А4, С23 и т.д.

Абсолютная ссылка в формуле используется при указании фиксированного адреса ячейки. При перемещении и копировании формулы абсолютные ссылки не изменяются. Это необходимо, когда нужно, например, умножить все числа из одного столбца на какое-либо одно число из другого. Для этого нужно "зафиксировать" ячейку, содержащую это число. Задачи такого рода решаются, например, при пересчете цен на товар из рублей в доллары. Для этого заводится ячейка с курсом доллара и в формуле перед ее адресом проставляется знак $, например, $A$2. Можно выделить в строке формул эту ячейку и нажать клавишу . Тогда значки "доллара" присвоятся автоматически. Если символ "доллара" поставить только перед буквой, то координата столбца абсолютная, а строки относительная и наоборот. Примеры: $A2, A$2. Такие ссылки называют смешанными.

Встроенные функции.

Часто при вычислении используются формулы, содержащие функции. В ЭТ имеются несколько сотен функций, которые подразделяются на Математические, Статистические, Финансовые и т.д.

Рассмотрим математические функции, которые могут пригодиться при больших объемах вычислений.

На панели инструментов стандартная расположена кнопка со значком , отвечающая за автосуммирование чисел с помощью функции СУММ. Для осуществления этой функции необходимо перетащить указатель мыши по ячейкам, которые нужно просуммировать. Будет выделен диапазон для суммирования, например А1:А100, при нажатии , в ячейке А111 будет рассчитана сумма всех чисел в столбце. Если необходимо рассчитать сумму в другом диапазоне, его нужно откорректировать.

Составление таблицы значений функции без использования Мастера функций. Алгоритм.

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

    Таблица значений функции y = x3
    -3-2-10123
           
  2. В ячейке набрать =А1^3. Примечание. А1 - адрес ячейки с первым значением аргумента (в нашем случае -3), ^ - знак степени, число 3 - показатель степени. Зафиксировать данные в ячейке.
  3. Потянуть за маркер заполнения вправо. Формула копируется для всех значений аргумента. Получаем следующую таблицу.

    Таблица значений функции y = x3
    -3-2-10123
    -27-8-101827

Для более сложных функций можно воспользоваться Мастером функций. Рассмотрим построение этой же функции другим способом.

Составление таблицы значений функции с использованием Мастера функций. Алгоритм.

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

    Таблица значений функции y = x3
    -3-2-10123
           
  2. Ввести команду Вставка-Функция.
  3. В окне диалога мастер функций:
    в списке Категории выбрать Математические;
    в списке Функции выбрать вариант Степень;
    нажать ОК.
  4. Ввести значения числа и значения показателя степени. Можно щелкнуть по нужной ячейке таблицы.
  5. Заполнить ряд значений функции с помощью команды Правка-Заполнить-Вправо.

Построение диаграмм и графиков.

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

Диаграмма - это графическое представление данных рабочего листа.

Необходимо правильно определиться с типом диаграммы, что зависит от отображаемых данных и цели построения диаграммы.

Тип диаграммы Область применения
Линейчатая, гистограмма Показывает изменение в течение некоторого периода времени
Круговая, кольцевая Отражает соотношение частей и целого. Можно показать только один ряд значений.
График Отображает тенденцию изменения данных за равные промежутки времени
График с областями, поверхность Показывает изменение общего количества в течение некоторого периода времени, отображая сумму введенных значений.

Мастер диаграмма запускается соответствующей пиктограммой или с помощью команды Вставка-Диаграмма.

Пример. Построим диаграмму для таблицы следующего содержания.

Имя Потребление мороженого в день, шт.
Катя 3
Маша 1
Коля 5
Петя 7

  1. Выделяем таблицу, включая заголовки.
  2. Работаем под управлением Мастера диаграмм. Щелкаем на пиктограмму Мастера, затем на свободном пространстве рабочего листа щелкаем мышью.
  3. Отвечаем на вопросы Мастера диаграмм. Соглашаемся с диапазоном ячеек и нажимаем кнопку Далее.
  4. Выбираем простую круговую диаграмму.
  5. Соглашаемся с утверждением, что ряды данных находятся в столбцах, а первая строка считается заголовком диаграммы (в нашем случае это так и есть, но возможны и другие варианты).
  6. На вопрос Добавить легенду? отвечаем положительно. Легенда содержит пояснения к диаграмме.
  7. В самом конце нажимаем клавишу Готово.

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

Пример. Построим график функции y = x3, приведенной ранее.

Таблица значений функции y = x3

-3-2-10123
-27-8-101827

  1. Аналогично предыдущему примеру выделяем диапазон данных, запускаем Мастер диаграмм выбираем график.
  2. на четвертом шаге Мастера в поле Считать _ стр. метками оси Х устанавливаем значение 1. В противном случае получится два графика по данным обеих строк.

График построен. Его особенностью является то, что "задействована" только положительная область оси Х. В новых версиях приложения есть вкладка "по точкам", с помощью которой график математической функции строится без особых проблем. Если же мы пользуемся вкладкой график, то область построения необходимо откорректировать. Для этого, щелкнув по оси Х, откроем закладку Формат оси, в разделе пересечение с осью У значений в категории номер ___ поставим 4 (что и соответствует действительности). Теперь график приобрел более правдоподобный вид. Можно добавить промежуточные значения Х, тогда он будет более "сглаженным".

Excel как базы данных.

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

Рассмотрим команды Сортировки и Фильтра данных. Предположим, что имеются данные о сотрудниках некоторого предприятия.

Фамилия Имя Отчество Год рождения Должность Заработная плата
ПетровСергейИванович19551-й зам. директора9 000р.
ВасильеваМарияПавловна1962инспектор отдела кадров5 000р.
КотовСергейИванович1971менеджер7 000р.
ЛисовИванСергеевич1981курьер2 000р.
ЗайцеваИринаИгоревна1979секретарь-референт4 000р.

Заголовки столбцов в терминах БД называются Полями, а каждая строка - записью. Итак, у нас имеется 6 полей и 5 записей со сведениями о сотрудниках.

Применим к данному фрагменту сведений о сотрудниках команду сортировки. Отсортируем, например их зарплату по возрастанию, или, иными словами, применим команду Сортировка к полю Заработная плата.

При сортировке данные выстраиваются в следующем порядке.

Числа сортируются от наименьшего отрицательного до наибольшего положительного.

Текст сортируется: числа, знаки латинский алфавит, русский алфавит.

Логическое значение Ложь предшествует значению Истина.

Все ошибочные значения равны.

пустые ячейки помещаются в конец списка.

Этапы сортировки данных.

  1. Выполняем команду Данные-Сортировка. Выбираем поле Заработная плата в списке Сортировать по и устанавливаем переключатель в положение по возрастанию.
  2. После нажатия кнопки ОК получаем отсортированную таблицу, где лидирует г-н Лисов с наименьшей зарплатой, запись с данными которого переместилась в начало таблицы. Как видим, изменился порядок следования строк, но сохранилась их целостность.
Фамилия Имя Отчество Год рождения Должность Заработная плата
ЛисовИванСергеевич1981курьер2 000р.
ЗайцеваИринаИгоревна1979секретарь-референт4 000р.
ВасильеваМарияПавловна1962инспектор отдела кадров5 000р.
КотовСергейИванович1971менеджер7 000р.
ПетровСергейИванович19551-й зам. директора9 000р.

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

Поиск данных. Фильтрация.

Пример. Необходимо найти всех сотрудников старше 40 лет. Это условие называется фильтром.

  1. Выделим диапазон поиска - всю таблицу. Введем команду Данные-Фильтр-Автофильтр. В названиях столбцов таблицы появятся раскрывающиеся списки, содержащие стандартные условия поиска.
  2. Развернем список в поле Год рождения и выберем Условие.... Сотрудники, старше 40 лет - это те, чей год рождения меньше, чем 1962 (по состоянию на 2002 год). Будем считать, только полные года.
  3. Введем оператор условия меньше и значение 1962.
  4. В результате найдена одна запись, удовлетворяющая заданному фильтру.
Фамилия Имя Отчество Год рождения Должность Заработная плата
ПетровСергейИванович19551-й зам. директора9 000р.

Таким образом, можно найти, например, всех сотрудников с именем Сергей, всех сотрудников с заработной платой больше 4000 р. и т.д. Можно объединять условия логическими выражениями, например, если поставить фильтр все сотрудники , с заработной платой больше 2 000 р., но меньше 5 000 р., то результатом такого поиска будет одна запись - Зайцева И.И.

Рекомендации к теме

Электронные таблицы MS Excel, на наш взгляд, самый "загадочный" продукт MS Office. Работа с ним требует серьезной квалификации, однако, даже большой стаж работы не гарантирует знания всех тонкостей этого приложения. Прежде всего потому, что в ней есть ряд функций, не всегда удобных в пользовании, например, для расчета параметров математические функции гораздо более удобно специальные математические пакеты, такие, как Mathcad, BLP, Statgraphics и др.

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

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


Рекомендованная литература:
  • Ефимова О., Морозов В., Угринович Н. Курс компьютерной технологии с основами информатики. Учебное пособие для старших классов. - М.: ООО "Издательство АСТ"; АВF, 2000 г.
  • Угринович Н. Информатика и информационные технологии. 10-11 класс- М.: Лаборатория Базовых Знаний, АО "Московские учебники", 2001 г.
  • Задачник-практикум по информатике. В 2-х томах /Под ред. И.Семакина, Е.Хеннера. - М.: Лаборатория Базовых Знаний, 2001 г.