Раздел 4. Обработка числовой информации

1. Электронные таблицы Excel

2. Построение диаграмм в Excel 2007

3. Тест на знание программы Excel

1. Электронные таблицы Excel

В 1979 году выпускник Гарвардского университета Дэн Бриклин и профессиональный программист Боб Франкстон предложили миру способ автоматизации трудоемких и утомительных операций пересчета, которые всегда требуются при ручном составлении таблиц в бухгалтерском и банковском учете, в проектно-сметных работах, при решении планово-экономических задач. Это была программа VisiCalc, которая позволяла представлять данные в виде таблицы на экране дисплея, прокручивать эту электронную таблицу по строкам и столбцам и, самое главное, обладала удивительным свойством – в таблице проводился автоматический пересчет содержимого ячеек при изменении значений одной из них. Причем все действия были очень наглядны – всё выполнялось в соответствии с принципом WYSIWYG («What You See Is What You Get»).

Популярность этой программы была феноменальной. Естественно, в первую очередь ею заинтересовались представители делового мира, что, кстати, заметно стимулировало продажу персональных компьютеров. По некоторым оценкам, более четверти проданных машин Apple II в 1979 году покупались для того, чтобы получить возможность работать на VisiCalc. Компьютер превратился в неотъемлемый инструмент бизнеса.

Успех первой электронной таблицы вызвал множество подражаний и вариантов, сопровождавшихся шумными судебными разбирательствами. Текущая версия для платформы Windows — Excel 14, также известная как Microsoft Office Excel 2010. Текущая версия для платформы Mac OS X — Microsoft Excel 2011.

В начале своего пути название Excel стало причиной иска о товарном знаке от другой компании, уже продававшей пакет программ под названием «Excel». В результате спора Microsoft была обязана использовать название «Microsoft Excel», а не «Excel» во всех своих официальных пресс-релизах и юридических документах. Однако со временем эта практика была позабыта, и Microsoft окончательно устранила проблему, приобретя товарный знак другой программы. Microsoft также решила использовать буквы XL как сокращённое название программы: иконка Windows-программы состоит из стилизованного изображения этих двух букв, а расширение файлов по умолчанию в Excel — .xls.

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

Excel предлагает широкий набор функциональных средств по обработке табличных данных:

• создание и редактирование электронных таблиц с применением богатого набора встроенных функций;

• оформление и печать электронных таблиц;

• построение диаграмм и графиков различной степени наглядности и детализации;

• работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из различных таблиц, в том числе из внешних баз данных;

• решение экономических задач типа «что – если» путем подбора параметров;

• решение оптимизационных задач;

• численное решение разнообразных математических задач;

• статистическая обработка данных;

• использование интегрированной среды разработки собственных программ макросов на основе языка программирования Visual Basic for Applications.

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

Если текст не входит в ячейку, то можно:

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

– объединить несколько ячеек и в них записать текст. Для этого необходимо выделить несколько соседних ячеек и нажать на пиктограмму – «Объединить и поместить в центре» меню Главная.

– организовать перенос текста в ячейке по словам: Формат ячеек –> Выравнивание –> Переносить по словам, где Формат ячеек вызывается с славиатуры (Ctrl+1) или правой кнопкой мыши – пункт контекстного меню.

Чтобы при наборе текста перейти к новой строке не выходя из ячейки, используйте разделитель Alt+Enter.

В тех случаях, когда число не входит в ячейку, Excel отображает его либо в экспоненциальной форме (1230000000 ® 1,23Е+09), либо вместо числа ставит решетки ####. В таком случает необходимо раздвинуть границы ячейки. В Excel можно выбрать различные числовые форматы представления чисел: Формат ячеек –> Число.

Формулы в Excel представляют собой арифметическое или логическое выражение, состоящее из чисел, адресов ячеек и функций, соединенных между собой знаками арифметических операций и операций отношения, и начинающееся со знака =. При её записи следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняются слева направо в порядке старшинства (возведение в степень ^ , умножение *, деление /, сложение +, вычитание -). Для изменения порядка выполнения операций используются круглые скобки, аргумент функции также берется в круглые скобки. Адреса ячеек набираются только латинским шрифтом, написание адреса ячейки можно заменить щёлчком мыши по соответствующей ячейке.

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

Переход в режим редактирования данных можно осуществить также щелчком по строке формул.

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

В Excel существует специфическая функция автозаполнения, полезная при вводе рядов данных. Если ввести в две соседние ячейки последовательно два числа, составляющие начало арифметической прогрессии, например, 1 и 2, затем их выделить и, как при копировании, с помощью маркера заполнения протащить их на несколько ячеек, то ряд продолжится: 1, 2, 3, 4 и т. д. Автозаполнению в Excel подлежат и нечисловые последовательности. Например, если ввести в две соседние ячейки Январь и Февраль и осуществить описанную выше операцию, то в следующих ячейках появятся Март, Апрель и т. д. Эти последовательности, или списки, можно сформировать самому и дать Excel запомнить их. Если автозаполнению подвергается формула, то при перемещении формулы вниз – увеличиваются номера строк, а при смещении вправо – имена столбцов, если указаны относительные адреса ячеек, абсолютные адреса содержат символ $ перед именем столбца и/или номером строки.

Excel имеет специальный аппарат для графического анализа моделей, в том числе построения аппроксимационных зависимостей (линий тренда P(x)) по данной таблице {x_i , y_i}, которые приближенно отражают функциональную связь y = f(x). С помощью регрессионного анализа можно показать тенденцию изменения рядов данных, экстраполировать их (то есть продолжить линию тренда вперед или назад за пределы известных данных).

Excel позволяет выбрать один из шести типов линии тренда – линейный, логарифмический, экспоненциальный, степенной, линейной фильтрации или полиномиальный (2,...,6 степени) и проверить (по различным критериям), какой из типов лучше всего подходит в данной ситуации. На рис. 22 приведён график с маркёрами некоторой последовательности чисел и добавлены две аппроксимации – линейный и кубический тренды.

Рис. 22. Две линии тренда: линейная и степенная

В качестве критерия может служить или критерий R² (коэффициент детерминации), автоматическое вычисление которого встроено в диалоговое окно Линия тренда, или квадратичное отклонение теоретических значений P_i от экспериментальных у_i

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

Чем меньше квадратичное отклонение, тем лучше линия тренда аппроксимирует ряд данных. Или, чем ближе коэффициент детерминации к единице, тем лучше тренд.