Сортировка, поиск и замена данных.

Электронные таблицы позволяют осуществлять сортировку данных. Данные в электронных таблицах можно сортировать по возрастанию или по убыванию. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в указанном порядке по данным определенного столбца, В качестве ключа сортировки может быть использованы: алфавит, числа или хронологический порядок. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком необходимо применять пользовательский порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» — следующими и «Высокий» — последними. Можно также проводить вложенные сортировки, т.е. сортировать данные по нескольким столбцам, при этом назначается последовательность сортировки столбцов.

Примечание.

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

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

9.5.Вычисления в электронных таблицах.

9.5.1.Формулы и функции в электронных таблицах.

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

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

	A B C

Пример: Приведем арифметические и

логические формулы и результаты

их вычислений по данным рис.9.2:

Рис.9.2.

Формула	Результат вычислений	Комментарий
=А1+В1*3		Содержимое ячейки В1 умножается на 3, и результат складывается с содержимым ячейки А1. (Умножение выполняется первым).
=А2-В3+С2	-3	Содержимое ячейки В3 вычитается из содержимого ячейки А2, а затем к результату добавляется содержимое ячейки С2. (Сложение и вычитание как действия одного уровня выполняются слева направо).
=В2/(С1*А2)		Содержимое ячейки С1 умножается на содержимое А2, и затем содержимое ячейки В2 делится на полученный результат. (Любые действия в скобках выполняются первыми).
=В1^С1-В2/А3		Содержимое ячейки В1 возводится в степень, определяемую содержимым ячейки С1, затем определяется частное от деления содержимого ячейки В2 на содержимое ячейки А3. Полученное частное вычитается из первого результата. ( Возведение в степень выполняется первым, затем выполняется деление и только потом - вычитание).
=А1>0#AND#C3<7		Поскольку содержимое ячеек А1 (3>0) и С3 (6<7) удовлетворяет заданным условиям, то всему выражению присваивается численная величина 1 ('Истинно').

Примечание.

В рассмотренном примере в формулах, кроме арифметических символов сложения (+), умножения (*), вычитания (-), деления (/) используются символ возведения в степень (^), символы сравнения (> – больше, < – меньше, к которым также относятся = – равно, >= – больше или равно, <= – меньше или равно), а также логический оператор И (#AND#). К последним еще относятся операторы ИЛИ (#OR#) и НЕ (#NOT#).

Как видно из примера результатом вычисления арифметической формулы является число. Логические формулы определяют, истинно выражение или ложно. Истиннымвыражениям присваивается численная величина 1, а ложным– 0.

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

Определение. Функция — это встроенная формула, выполняющая конкретную операцию, иными словами, функциейназывается зависимость некоторой переменной у от одной х или нескольких переменных х₁ , х₂ , . . . , x_n. Причем каждому набору значений переменных х₁ , х₂ , . . . , x_n будет соответствовать единственное значение определенного типа зависимой переменной y.

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

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

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

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

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

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

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

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

Пример: Ниже приведены наиболее часто встречающихся функции:

§ SUM(Список) - математическая функция определения суммы всех числовых значений в списке. Список может состоять из адресов ячеек и блоков, а также числовых значений.

SUM(B5..E5),

SUM(A3..E3, 230).

§ AVERAGE(Список) - статистическая функция определения среднего арифметического значения всех перечисленных в списке величин.

AVERAGE(5, 20, 10, 5),

AVERAGE(B10..B13, B17).

§ МАХ(Список) - статистическая функция, результатом которой является максимальное значение в указанном списке.

МАХ(В3..В8,А3.,А6).

§ IF(Условие, Истинно, Ложно) - логическая функция, проверяющая на истинность заданное логическое условие. Если условие выполняется, то результатом функции является значение аргумента "Истинно". Если условие не выполняется, то результатом функции становится значение аргумента "Ложно".

IF(B4<100, 100, 200)- если ячейка В4 содержит число меньше 100, то функции присваивается значение 100, если же это условие не выполняется (т.е. содержимое ячейки В4 больше или равно 100), функции присваивается значение 200.

В русскоязычной версии табличного процессора Microsoft Excel эти же функции записывается следующим образом:

Сумм(B5..E5), сумм(A3..E3, 230),

СРЗНАЧ(B2:B11),

МАКС(В3..В8,А3.,А6),

ЕСЛИ(B4<100, 100, 200).

Примечание.

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