Составитель: Лихачева И.О., учитель информатики

Основы вычислений в электронных таблицах OpenOffice.org Calc

1.ВВЕДЕНИЕ

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

Разработкой и распространением табличных процессоров (программ ЭТ) занимаются как коммерческие компании, так и группы разработчиков свободного программного обеспечения (точнее, проектов FOSS – Free and Open Source Software). Один из самых известных проектов FOSS – интегрированный офисный пакет OpenOffice.org - также имеет в своем составе табличный процессор OpenOffice.org Calc (в дальнейшем, для краткости, будем использовать обозначение OOo Calc). Этот табличный процессор с успехом может использоваться для решения экономических и инженерно-технических задач.

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

2.ИНТЕРФЕЙС ЭЛЕКТРОННОЙ ТАБЛИЦЫ

В интерфейсе программ ЭТ различают понятия "окно программы" и "рабочее поле программы". Окно программы ЭТ устроено стандартным для графического интерфейса образом и содержит строку заголовка окна, строку главного меню, панели инструментов (как минимум, панели "стандартная" и "форматирование"), а также строку состояния (рис. 1). Нестандартным элементом окна, свойственным только программам ЭТ, является строка ввода, в которой отображается и редактируется помещенная в таблицу информация.

Слева от строки ввода расположены три важные кнопки: кнопка " =", обеспечивающая переход в режим ввода формул, кнопка автосуммирования " S" и кнопка вызова Мастера функций " f(x)".

Рис. 1. Элементы интерфейса электронной таблицы

 Рабочее поле программы представляет из себя совокупность листов, каждый из которых состоит из ячеек. Каждая ячейка имеет адрес, определяемый строкой и столбцом, на пересечении которых находится ячейка. Столбцы, как правило, обозначаются буквами латинского алфавита (1 или 2 буквы), строки – числами. Сетку, которая “расчерчивает” лист на ячейки, при желании можно сделать невидимой. Всегда одна из ячеек является активной. Активная ячейка – это та ячейка, в которую в данный момент осуществляется ввод или содержание которой редактируется. Активная ячейка всегда выделена (обычно “жирной” рамкой). Перемещение указателя активной ячейки осуществляется клавишами-стрелками на клавиатуре или щелчком левой кнопкой мыши в любом месте листа. Ввод информации в позицию активной ячейки осуществляется либо полностью с помощью клавиатуры, либо (в случае построения формул с функциями) с помощью действий “вставка функции” ("Вставка/Функция...") из главного меню или панели инструментов.

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

Слева от ярлычков листов имеются кнопки перемещения по листам. Пока листов мало, они неактивны.

В каждой ячейке ЭТ могут быть записаны числа, текст или формулы. Особым случаем числа является календарная дата. Числа автоматически выравниваются по правому краю ячеек, а текст – по левому. Содержание активной ячейки отображается в строке ввода, а для редактирования содержания активной ячейки надо нажать клавишу [F2] и после этого редактировать содержание строки ввода как текст. Редактирование завершается нажатием на [ENTER]. Для удаления содержания ячейки (или блока ячеек) достаточно нажать клавишу [DEL].

Каждая ячейка ЭТ имеет адрес, который записывается как комбинация буквы столбца и номера строки (напр. A8, C67, X999). Для указания блока (диапазона) ячеек указываются адреса левой верхней и правой нижней ячеек через двоеточие (например, A3:C18, B1:B46, C3:C12).
Операции редактирования (копирования, перемещения, удаления и вставки) можно делать с ячейками или блоками ячеек (копирование, перемещение или удаление), со строками, столбцами, группами строк и столбцов (удаление или вставка). Перед операцией редактирования нужно выделить объект для выполнения операции. Выделение групп ячеек делается "протаскиванием" мыши с нажатой левой кнопкой от левого верхнего угла нужного блока до нижнего правого угла, а выделение строк и столбцов – щелчком левой кнопкой мыши по номеру строки (букве столбца). Для выделения группы строк или столбцов тоже надо "протащить" указатель мыши.

Каждый лист ЭТ OOo Calc может содержать до 245 столбцов и до 65000 строк (примерно), а в файле ЭТ может храниться до 256 таких листов. В каждую ячейку может быть записан текст или формула длиной до 256 символов.

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

3.ФОРМУЛЫ. АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ

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

Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 2. При вводе исходных данных полезно отключить режим автоматической проверки орфографии. Для исправления ошибок в ячейках электронной таблицы используется режим редактироваия строки ввода, который включается клавишей [F2]. Завершение редактирования обеспечивается клавишами [ENTER] (с сохранением изменений) или [ESC] (без сохранения изменений).

Рис. 2. Исходные данные для примера вычислений

Если при вводе информации ширина ячейки представляется недостаточной, ее можно скорректировать после завершения ввода всех данных. В ЭТ есть возможность подобрать ширину столбца автоматически ("Формат/Столбец/Оптимальная ширина...").

Для вычисления заработка нужно просто перемножить попарно числа из третьей (столбец C) и четвертой (столбец D) колонок. Результаты вычислений должны быть в пятой колонке (столбец E). С учетом возможностей ЭТ, формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (в нашем примере – в ячейке E4, под заголовком "Заработок"). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу “=” (указание на начало ввода формулы). После этого щелкаем левой кнопкой по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – “*”) и щелкаем левой кнопкой по ячейке с количеством отработанных дней (D4), после чего нажимаем

Теперь скопируем эту формулу в оставшиеся ячейки. Поместив указатель активной ячейки на E4, в главном меню выберем команду копирования ("Правка/Копировать"). После этого выделим ячейки E5:E11 и вызовем команду вставки ("Правка/Вставить"). Результаты показаны на рис. 3.

Рис. 3. Результаты вычисления и копирования формулы.

Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.

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

Если в какой-либо ячейке расчетного столбца (столбца "Заработок") перейти в режим редактирования (

На следующем этапе посчитаем налог на доходы физических лиц, который будет начислен на рассчитанные ранее значения заработка. Пусть ставка налога фиксирована и составляет 13%. Тогда наша таблица дополняется в соответствии с рис. 4.

Рис. 4. Добавление параметра для вычислений.

Сумму налога легко сосчитать по правилу "Сумма налога = заработок*ставка_налога". Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 5а).

Рис. 5а. Неправильная организация вычислений с параметром.

В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (заработка первого в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 5а). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс "$" и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов "$" при редактировании формулы можно использовать комбинацию клавиш

Рис. 5б. Правильная организация вычислений с параметром.

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

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

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

4.ВСТРОЕННЫЕ ФУНКЦИИ И "МАСТЕР ФУНКЦИЙ"

Программа электронной таблицы OOo Calc позволяет использовать несколько сотен встроенных функций различных категорий.

Каждая функция имеет скобки, в которых записываются аргументы функции (например SIN(A8), PRODUCT(число1;число2;…), PI() ). В электронной таблице в качестве аргумента может быть указано число, адрес ячейки, диапазон адресов ячеек, другая функция или не указано ничего (как в функциях PI() или TRUE() ).

Функции участвуют в формулах для вычислений. Для построения формул с функциями в ЭТ OOo Calc имеется система автоматизированного построения формул ("Мастер функций"). Вызывается эта система через команды меню "Вставка/Функция..." или нажатием на кнопку f(x) на панели инструментов программы ЭТ. Вариант окна для выбора функций показан на рис. 6. Для упрощения выбора функции сгруппированы по категориям.

Рис. 6. Диалог выбора функции.

При выборе конкретной функции "Мастер функций" помогает правильно построить формулу в интерактивном режиме.

Любая функция может быть аргументом другой функции. В "Мастере функций" при указании аргументов можно использовать кнопки fx слева от поля ввода для вставки функции в качестве аргумента (рис. 7). Количество вложенных функций ограничено емкостью ячейки ЭТ - вся конструкция не должна превышать длину в 256 символов.

Рис. 7. Определение аргументов функции.

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

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

5.МАТЕМАТИЧЕСКИЕ ФУНКЦИИ

Знакомство со встроенными функциями начнем с категории математических функций.

Обычно имеется несколько десятков математических функций. Обязательно наличие тригонометрических функций (sin, cos, tg и т.д.) и функций преобразования чисел или результатов вычислений (квадратный корень, целая часть, остаток от деления и пр.). Особое место занимает функция суммирования (SUM() ).

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

Таблица функций

Более подробное и полное описание всех функций (не только математических) можно найти в документации по OpenOffice.org Calc (http://documentation.openoffice.org/manuals/oooauthors2/index.html ).

Функции COUNTIF() и SUMIF() занимают особое место, поскольку позволяют проводить вычисления при выполнении каких-то условий. Более подробно они будут подробно рассмотрены позже.

Для примера рассмотрим использование функции SUM(). В описанной выше задаче посчитаем общую сумму налогов. Для этого перейдем в ячейку под столбцом налогов (например, в F13) и вызовем "Мастер функций". Выберем категорию "Математические", среди математических найдем функцию SUM и на следующем шаге укажем аргумент в виде диапазона ячеек F4:F11. Диапазон можно либо ввести вручную, либо свернуть диалоговое окно и выделить ячейки с суммами налогов с помощью мыши. Завершается формула, как обычно, нажатием на клавишу

Однако в ЭТ для суммирования ячеек есть специальная кнопка "Автосуммирование" (см. описание интерфейса). Используем эту возможность для вычисления суммы всех заработков. Для этого поместим указатель активной ячейки в E13 и нажмем на кнопку "Автосуммирование". Получим результат, показанный на рис. 8.

Рис. 8. Использование автосуммирования.

При использовании автосуммирования автоматически выбирается непрерывный диапазон чисел выше или левее ячейки с результатом. Пустая ячейка считается нулем. При желании можно исправить адреса ячеек непосредственно в строке ввода. Нажатие на

6.ДИАГРАММЫ

Построим в OOo Calc двумерную и трехмерную диаграммы по результатам работы некоторых математических функций.

В качестве двумерной (2D) диаграммы построим график функции F(x)=exp(-x)cos(Nx), где N – натуральное число. Для построения графика сначала нужно построить таблицу данных. Пусть данные располагаются по столбцам, переменная x изменяется в диапазоне от 0 до 3 радиан, шаг по x составляет 0,1 радиана. Для получения значений x удобно к первому значению прибавить шаг, а потом скопировать формулу. Пример данных показан на рис. 9.

Рис. 9. Фрагмент таблицы данных для графика.

Для построения графика выделяем всю область данных, включая заголовки столбцов, а затем выбираем в меню команду "Вставка/Диаграмма...".

Далее следует серия диалоговых окон, в которых можно выбрать данные для подписей, лист для диаграммы, тип и вид диаграммы. Выбираем для построения диаграммы новый лист, тип диаграммы – "XY", вариант графика – B-сплайн. Определяем заголовок диаграммы и подписи по осям X и Y. После нажатия на кнопку "ГОТОВО" диаграмма появляется на указанном листе. При необходимости можно изменить размер диаграммы с помощью мыши. Настройка вида диаграммы (графика) осуществляется выделением нужных объектов, для которых правой кнопкой мыши вызывается контекстное меню. Можно изменять вид объектов диаграммы, делать их видимыми или невидимыми. Окончательный вид графика показан на рис. 10. Попробуйте добиться такого же.

Рис. 10. График функции F(x)=exp(-x)cos(Nx), N=3.

Пусть x изменяется сверху вниз, а y – слева направо. Сделаем сетку чисел 20x20 в соответствии с заданной функцией, учитывая, что формулы можно копировать не только по вертикали, но и по горизонтали. При копировании формул не надо забывать про возможность указания абсолютного адреса для строки или столбца.

В диалогах построения диаграммы выбираем опять-таки новый лист для диаграммы, тип диаграммы – трехмерные линии. Отказываемся от легенды, т.к. каждый столбец интерпретируется как отдельный ряд данных. Результат показан на рис. 11.

Рис. 11. Трехмерная диаграмма.

 Видно, что построение 3D-диаграмм не является сильной стороной OOo Calc. Здесь диаграммы квази-трехмерные, глубина используется как средство придания выразительности. Для построения настоящих трехмерных диаграмм можно использовать свободные математические пакеты Maxima или SciLab, рассмотрение которых далеко выходит за рамки настоящего пособия.3.

7. ЛОГИЧЕСКИЕ ФУНКЦИИ

Логические функции используются для вычислений с условиями и формирования этих условий. Если условие выполняется, выполняется один набор команд, а если не выполняется – другой набор команд. Для формирования условий в формулах ЭТ используется функция IF(). Она имеет три аргумента. Первый аргумент – условие, второй аргумент – формула, которая работает при выполнении условия, третий аргумент – формула, которая работает если условие не выполняется.

Пусть, например, ячейка D5 содержит формулу "=IF(A1

В аргументах функции IF() могут использоваться другие логические функции – AND(), OR(), NOT().

Функция AND() (логическое "И") имеет формат:
AND(условие1 ,условие2, ...условиеN).

Она принимает значение "логическая 1" (т.е. "ИСТИНА" - TRUE), если выполняются все условия. В остальных случаях функция AND() принимает значение "логический 0" (т.е. "ЛОЖЬ" - FALSE).

Функция OR() (логическое ИЛИ) имеет формат:
OR(условие1 ,условие2 ... условиеN ).

Она принимает значение "логический 0" (т.е. "ЛОЖЬ" - FALSE), если не выполняется ни одно из условий. В остальных случаях функция OR принимает значение "логическая 1" (т.е. "ИСТИНА" - TRUE).

Функция NOT (логическое отрицание) имеет формат:
NOT(условие).

Эта функция принимает значение "ИСТИНА", если условие не выполняется и наоборот.

В OOo Calc определены еще две логические функции – TRUE() и FALSE(). Они выдают значения соответственно ИСТИНА и ЛОЖЬ.

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

Например, сложная функция

=IF(A1

выполняет следующие действия: если значение в ячейке A1 меньше 100, то выводится текстовое значение "ку-ку". В противном случае проверяется условие вложенной функции IF(). Если значение в ячейке A1 равно 100 выводится текстовое значение "ура", иначе выводится значение из ячейки C1. Toт же результат может быть получен с помощью выражения

=IF(A1

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

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

Итак, заданы три коэффициента квадратного уравнения (A, B и C). Перед вычислением корней вычислим предварительно значение дискриминанта D и его будем использовать при формировании формул для вычисления корней. Таблица с данными и с формулами показана на рис. 12 (формулы показаны справа от ячеек, в которых они реально работают). Самостоятельно проверьте варианты с D=0 и D>0.

Рис. 12. Задача о решении квадратного уравнения.

8.КАЛЕНДАРНЫЕ ФУНКЦИИ.

Календарные функции в программах ЭТ предназначены для работы с датами. Дата в программе ЭТ формируется с использованием системного таймера как количество дней, прошедших с какого-то начала отсчета. ЭТ OOo Calc позволяет задавать начальную дату ("нулевой день"). Количество дней, прошедших от нулевого, преобразуется с помощью календаря операционной системы в структуру ДД/ММ/ГГГГ, где ДД – номер дня в месяце, ММ – номер месяца в году, ГГГГ – номер года нашей эры (например, 28/04/2003 означает 28 апреля 2003 года). В зависимости от формата ячейки, содержащей дату, представление может быть изменено (например, вместо 28/04/2003 получится 28 апр.)

Календарные функции обычно находятся в категории “Дата и время”, однако в настоящем пособии функции для работы со временем (т.е. часами, минутами, секундами) не рассматриваются.

Рассмотрим основные функции для работы с датами.

Функция TODAY() не имеет аргументов и выдает значение текущей даты в стандартном формате.

Функции DAY(адрес_ячейки), MONTH(адрес_ячейки) и YEAR( адрес_ячейки) выдают, соответственно, номер дня в месяце, номер месяца в году и номер года для даты, записанной в ячейке " адрес_ячейки ".

Функции DAYS(нач.дата;кон.дата;тип), WEEKS(нач.дата;кон.дата;тип), MONTHS(нач.дата;кон.дата;тип), YEARS(нач.дата;кон.дата;тип) вычисляют соответственно количество дней, недель , месяцев или лет между двумя датами. Назначение аргумента "тип" будет понятно из приведенного ниже примера.
Функция DATE(адр1;адр2;адр3) формирует дату из трех чисел (номера года, номера месяца и номера дня), записанных соответственно в ячейках "адр1", "адр2", "адр3".

Функция WEEKDAY(адрес_ячейки;тип) выдает номер дня недели для даты, записанной в ячейке " адрес_ячейки". Обычно отсчет начинается с воскресенья ("тип" не указан или "тип"=1), как это принято в США. Если указать "тип"=2, то первым днем недели будет считаться понедельник.

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

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

Дан список лиц, для которых известны фамилии, пол и даты рождения. Определить:
a.День недели, на который приходится день рождения каждого человека в текущем году. Если день рождения приходится на выходные дни, вывести текст "УРА!", в остальных случаях вывести текст "УВЫ...".
b.Возраст на настоящий момент.
c.Дату выхода на пенсию для каждого человека.

Для этой задачи воспользуемся списком из задачи про доходы и налоги. Пол установим в соответствии с фамилиями, даты рождения введем произвольно (удобно вводить дату в виде 23/11/88, а программа приведет ее в нужный вид).

Для получения решения по пункту "a" необходимо проделать некоторые промежуточные вычисления. Сначала нужно для каждого лица сформировать дату рождения в текщем году на основании дня и месяца рождения, а также номера текщего года. Тогда первая формула (в ячейке D4 на рис. 13) будет иметь вид:

=DATE(YEAR(TODAY());MONTH(C4);DAY(C4))

Соответственно, формула для окончательного результата по пункту "а" (в ячейке E4) будет иметь вид:

=IF(OR(WEEKDAY(D4;2)=6;WEEKDAY(D4;2)=7);"УРА!";"УВЫ...")

Для обработки всего списка просто копируем эти формулы вниз.

Для получения решения по пункту "b" воспользуемся функцией YEARS() и запишем формулу для возраста в следующем виде:

=YEARS(C4;TODAY();0)

Поскольку возраст определяется как количество полных лет, прошедших с даты рождения, третий аргумент функции YEARS() нужно установить в 0. Если он будет установлен в 1, будет вычисляться просто разность годов (например, 2007-1956), что даст неверный результат.

Для получения решения по пункту "c" снова нужно формировать даты, используя значения параметров возраста выхода на пенсию. Эти возрасты на момент написания пособия составляют 60 лет для мужчин и 55 для женщин, однако они могут в любой момент измениться, поэтому конкретные числа в формулу записывать не будем. Дата выхода на пенсию формируется с использованием условия проверки пола. Итак, получаем формулу:

=IF(B4="М";DATE(YEAR(C4)+$G$1;MONTH(C4);DAY(C4));DATE(YEAR(C4)+$G$2;MONTH(C4);DAY(C4)))

Рис. 13. Задача на календарные функции.

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

Рис. 14. Структура формулы в "Мастере функций".

9.Выбор одного из многих вариантов. Ассоциативные массивы.

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

В программах ЭТ ассоциативные массивы реализуются как блоки ячеек (справочные таблицы), содержащие минимум два столбца. Первыи столбец содержит ключи, второй – значения, соответствующие ключам. Функции для работы с ассциативными массивами – LOOKUP(), VLOOKUP() и HLOOKUP() – находятся в категории "Электронная таблица".

Для примера рассмотрим следующую задачу. В ралли участвуют гонщики на автомобилях, для которых известны марки и расход топлива в литрах на 100 км. Дан список, в котором указаны фамилии гонщиков и марки автомобилей. Известна протяженность трассы гонок в километрах L. Определить расход топлива для каждого участника гонок.

Для решения задачи составим справочную таблицу для 7-ми разных марок а/м, и расположим ее в ячейках F3:G10 (рис. 15). Полезно соблюдать алфавитный порядок текстовых значений в первом столбике справочной таблицы. Длину трассы L, которая является параметром, запишем в ячейку E1.

Данные (список участников и марки их а/м) запишем в ячейки A3:B10 (рис. 16). Для вычисления полного расхода топлива используем формулу с функцией LOOKUP().

Формула в ячейке C4 будет выглядеть следующим образом.

=LOOKUP(B4;$F$3:$F$10;$G$3:$G$10)*$E$1/100

Функция LOOKUP() считывает содержание ячейки, указанной в первом аргументе, ищет это значение в столбце, указанном во втором аргументе и выдаёт соответствие этому значению в столбце, указанном в третьем аргументе. То есть, по названию а/м находим расход топлива на 100 км, а потом смотрим, сколько сотен километром проехали а/м и производим простое умножение. Принципиально важно указывать абсолютные адреса блоков ячеек справочной таблицы.

Итоговая таблица показана на рис. 17.

Рис. 17. Результат вычислений с использованием ассоциативного массива.

Ограничение функции LOOKUP() – только один столбец соответствий. Более "мощной" является функция VLOOKUP(), в которой второй аргумент определяет весь блок ячеек, содержащих ассоциативный массив, а третий аргумент указывает, в каком столбце ассоциативного массива нужно искать соответствие ключу. Четвертый (необязательный) аргумент определяет порядок сортировки первого ("ключевого") столбца. Если он не указан или равен 1 (логическая ИСТИНА) то первый столбец ассоциативного массива для функции VLOOKUP() должен содержать числа, отсортированные по возрастанию, или текст, отсортированный в алфавитном порядке. Если значения в первом столбце не отсортированы, то четвертый аргумент должен быть установлен в 0. Еще одним большим достоинством функции VLOOKUP() является возможность работы с диапазонами значений ключа.

Для примера рассмотрим вычисление суммы годового налога при прогрессивной налоговой шкале. Пусть при годовом доходе до 10000 у.е. ставка налога составляет 12%, до 30000 у.е. - 20%, до 50000 у.е. - 25% и при большем доходе - 35%. Для создания таблицы данных используем фамилии из предыдущего примера, а суммы годового дохода запишем такие, чтобы можно было реализовать все варианты ставок налога (рис. 18). Таблицу данных разместим в диапазоне A3:B10.

Рис. 18. Данные для задачи о вычислении налогов.

Справочную таблицу размещаем в диапазоне F3:G7 (рис. 19), формула в ячейке C4 с использованием функции VLOOKUP() выглядит следующим образом:

=VLOOKUP(B4;$F$3:$G$7;2)*B4

Рис. 19. Ассоциативный массив для функци VLOOKUP().

Во втором столбце ассоциативного массива находим ставку налога, соответствующую доходу, а затем получаем сумму налога, умножая ставку налога на величину дохода (рис 20).

Рис. 20. Использование VLOOKUP() для интервалов значений ключа.

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

Четвертый аргумент функции VLOOKUP() также влияет и на возможность интервального просмотра. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то интервальный просмотр работает, как описано выше. Если этот аргумент имеет значение ЛОЖЬ (0), то функция VLOOKUP() ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #N/A (#Н/Д – "нет данных"). Таким образом, для использования возможности работы с интервалами значений в VLOOKUP() первый столбец справочной таблицы (ассоциативного массива) обязательно должен быть отсортирован по возрастанию.

Функция HLOOKUP() работает аналогично VLOOKUP(), только порядок следования "ключей" – не сверху вниз, а слева направо.

LOOKUP(), VLOOKUP() и HLOOKUP() заменяют вложенные IF(), и в сочетании с логическими функциями составляют мощный инструмент обработки данных в электронных таблицах.

10.СТАТИСТИЧЕСКИЕ ФУНКЦИИ. ВЫЧИСЛЕНИЯ С КРИТЕРИЕМ.

Рассмотрим следующую задачу.

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

Для решения этой задачи нам понадобятся статистические функции MIN(), MAX() и AVERAGE(), а также функции для вычислений с условием COUNTIF() и SUMIF(), которые занимают особое место среди математических функций OOo Calc.

Сначала кратко изучим упомянутые функции.

MIN(число1;число2...) и MAX(число1;число2...) — позволяют найти минимальное и максимальное число среди аргументов, которые могут быть числами или ссылками на ячейки с числами. Может использоваться один аргумент – диапазон ячеек.

AVERAGE(число1;число2...) — позволяет найти среднее арифметическое аргументов. Также может использоваться один аргумент – диапазон ячеек.

COUNTIF(диапазон; условие) — позволяет подсчитать количество ячеек в заданном диапазоне, значения которых удовлетворяют некоторому условию.

SUMIF(диапазон; условие; диапазон_суммирования) — позволяет подсчитать сумму ячеек по заданному условию.

Теперь перейдем непосредственно к решению задачи.

Заполняем таблицу исходными данными, как показано на рис. 21.

Рис 21. Исходные данные для задачи о соревнованиях

Сразу под списком в соответствующих столбцах подсчитываем минимальный, максимальный и средний результаты (по столбцу С =MIN(C2:C16), =MAX(C2:C16),

=AVERAGE(C2:C16) и аналогично по столбцу D).

Затем подсчитываем количество юношей и количество девушек. В ячейку B22 вносим формулу

=COUNTIF(B2:B16;"юноша"),

а в ячейку B23 формулу

=COUNTIF(B2:B16;"девушка")

В ячейки C22 и C23 записываем формулы для подсчета суммы результатов по метанию для юношей и девушек соответственно

=SUMIF(B2:B16;"юноша";D2:D16)
=SUMIF(B2:B16;"девушка";D2:D16)

После чего подсчитываем среднее значение в ячейках D22 и D23, разделив сумму результатов на количество участников в каждой группе. Затем подсчитываем разницу средних значений (рис. 22).

Рис 22. Решение задачи о соревнованиях.

11.РАБОТА С МАССИВАМИ ПРИ АНАЛИЗЕ ДАННЫХ

Рассмотрим некоторые приемы анализа экономических данных на примере следующей задачи.

На основе эксперимента получена следующая зависимость посещаемости дискотеки от входной платы

Очевидно, что оптимуму соответствует максимальная выручка. Соответственно. решение задачи состоит в том, чтобы подсчитать выручку в каждом случае, найти максимальную и написать формулу, показывающую входную плату, соответствующую максимальной выручке. То есть нужно определить номер столбика, в котором получается максимальная выручка и вывести значение входной платы. Для решения задачи будем использовать функции INDEX() и MATCH() (категория "Электронная таблица"). Рассмотрим формат этих функций:

MATCH(искомое_значение; искомый_массив; тип_сопоставления) — находит позицию (порядковый номер) искомого значения в одномерном массиве. Значение аргумента "тип сопоставления" - (-1, 0 или 1) - зависит от того, упорядочен ли массив.
(-1 — массив упорядочен по убыванию, находится место наименьшего значения, которое больше или равно искомому; 0 — массив может быть неупорядоченным, находится место первого значения, равного исходному; 1 — массив упорядочен по возрастанию, находится место наибольшего значения, которое меньше или равно искомому).

Функция INDEX() имеет две формы. Рассмотрим более простую:

INDEX(массив; номер_строки; номер_столбца) — находит значение элемента, находящегося в заданном массиве на пересечении заданных строки и столбца.

Определяем выручку в каждом случае (умножив входную плату на количество посетителей), затем функцией MAX() находим наибольшую выручку. После этого функцией MATCH() определяем, на каком месте в массиве она находится и функцией INDEX() смотрим, какая входная плата находится на этом месте (рис. 23).

Рис. 23. Решение задачи о дискотеке.

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

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

Для решения задачи потребуется статистическая функция RANK() для распределения участников по местам, а также уже знакомый COUNTIF() для вычисления с условием. Однако условие для COUNTIF() обязательно должно быть текстом, поэтому при формировании условия с вычисляемыми данными целесообразно использовать текстовую функцию CONCATENATE(). Результат использования этих функций показан на рис. 24, причем формула с функцией RANK() приведена один раз, чтобы не загромождать иллюстрацию. Отметим, что для обеспечения возможности копирования формулы использованы абсолютные адреса диапазона.

Рис. 24. Распределение по местам и вычисление с условием.

 Как видно из рис. 24, функция RANK() имеет три аргумента. Первый – значение, место (ранг) которого определяется. Второй аргумент – диапазон, в котором происзодит распределение по местам. Наконец, третий аргумент – указатель порядка сортировки. Если третий аргумент 0 или не указан, места распределяются по убыванию значений (т.е. чем больше – тем лучше, 1-е место – максимальное знаение). Если же поставить 1, то места будут распределяться по возрастанию (т.е. чем меньше, тем лучше, как в рассмотренном случае).

Использование функции CONCATENATE() для формирования условия позволяет работать с вычисляемыми значениями, т.е. приведенную таблицу можно использовать для любых результатов. А если вынести знак сравнения также в отдельную ячейку, можно быстро менять условия анализа данных.