Технология обработки числовой информации

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

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

Наиболее популярными являются: электронная таблиц_а Microsoft Excel, Lotus 1-2-3, QuatroPro. Рассмотрим электронные таблицы на примере Microsoft Excel. Вычислительные возможнос­ти Microsoft Excel позволяют создавать любые документы, содер­жащие текстовые и числовые данные, рисунки, диаграммы.

Табличный процессор Excel используют:

• для решения математических задач (выполнения табличных вычислений, исследования функций решения уравнений);

• решения задач математического моделирования;

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

• ввода и редактирования текста;

• наглядного представления данных в виде таблиц и функций.
Рассмотрим компоненты окна Excel (рис. 5.15), дополняющие

основное окно Windows. Рабочий лист рабочей книги служит для хранения данных, разделенных на ячейки. Строка формул пред­назначена для редактирования содержимого ячейки. Покоси «ре­крутки применяются в тех случаях, когда весь текст не помещает­ся на экране и требует сдвига вверх-вниз или влево-вправо.

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

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

Рабочая книга Excel - совокупность рабочих листов, сохраня­емых на диске в одном файле. Документом, т. е. объектом обработ­ки Excel, является файл с произвольным именем и расширением 1 xis В каждом файле *.xls может размещаться одна книга, а в кни­ге - от 1 до 255 рабочих листов. По умолчанию в каждой книге содержится три рабочих листа. Рабочий лист имеет табличную структуру и может состоять из любого числа страниц.

Листы служат для организации и анализа данных. Одновременно на нескольких листах данные можно вводить, править, произ­водить с ними вычисления. Имена листов находятся на ярлычках, расположенных в нижней части окна книги. Для перехода с одного листа на другой необходимо указать соответствующий ярлычок. «Название текущего листа всегда выделено жирным шрифтом. По умолчанию листы именуются «Лист 1», «Лист 2» и т.п. Листы можно «Переименовывать, вставлять, удалять, перемещать или копиро­вать в пределах одной книги или из одной книги в другую.

На каждом листе электронной таблицы используется сетка, чтобы разделить лист на строки и столбцы. Заголовки столбцов Обозначаются буквами или сочетаниями букв (A, G, АВ и т.п.), заголовки строк — числами (1, 16, 278 и т.п.).

Ячейка — область электронной таблицы, находящаяся на месте «Пересечения столбца и строки и являющаяся наименьшей структурной единицей на рабочем листе. Каждая ячейка таблицы имеет свой собственный адрес. Адрес ячейки электронной таблицы составляется из заголовка столбца и заголовка строки, например: М, F123, R7.

В Microsoft Excel существует понятие активной (текущей) ячейки.

Цктивная ячейка — это ячейка, которая в данный момент способ­на воспринимать ввод с клавиатуры. Активная ячейка отображает­ся двумя способами: 1) в самой таблице (она подсвечена жирной линией), которая называется табличным курсором; 2) номер ячейки отображается в строке формул, которая рас­положена над панелью, содержащей названия столбцов. Взаимодействие пользователя с программой Microsoft Excel происходит с помощью:

• меню приложений;

• панели инструментов;

•строки формул;

• строки состояния.

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

Строка состояния — область в нижней части окна, отобража­ющая сведения о выбранной команде или выполняемой операции.

5.4.1. Типы данных, используемых в Microsoft Excel

В Microsoft Excel поддерживаются следующие типы данных: текст; числа; даты; формулы; функции.

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

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

Дата и время вводятся в ячейке как числа и выравниваются по правому краю. Программа Excel распознает внешние форматы дат, в которых день, месяц и год разделены точкой, косой чертой или пробелом. Например: 10.12.99, 10/12/99 или 10 декабря 1999.

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

Например, формула =А1+В2 обеспечивает сложение чисел, хранящихся в ячейках А1 и В2, а формула = А1*9 — умножение числа, хранящегося в ячейке А1, на 9. При вводе формулы в ячей­ке отображается не сама формула, а результат вычислений по этой формуле. При изменении исходных значений, входящих в форму­лу, результат пересчитывается немедленно.

В зависимости от решаемой задачи возникает необходимость применять различные форматы представления данных. В каждом конкретном случае важно выбрать наиболее подходящий формат. По умолчанию Excel использует для представления чисел «Чис­ловой» формат, который по умолчанию отображает два десятич­ных знака после запятой (например, 195,20).

Экспоненциальный формат применяется, если число, содержа­щее большое число разрядов, не помещается в ячейке (например, число 2 000 000 000 в экспоненциальном формате будет записано в следующем виде: 2,00Е + 09). Можно использовать также специа­лизированные форматы: «Денежный» формат удобен для бухгалтеpcких расчетов, форматы «Дата» и «Время» позволяют хранить значение временных данных, «Текстовый» формат предназначен для хранения текста и т. д.

Для выбора формата данных необходимо выбрать команду формат/Формат ячеек. В диалоговом окне Формат ячеек выбрать вкладку Число. В списке «Числовые форматы» выбрать необходи­мый формат, например «Числовой». С помощью счетчика Число Десятичных знаков установить необходимое число знаков после за­пятой. В области Отрицательные числа выбрать форму представле­ния отрицательных чисел.

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

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

В формулах используются ссылки на адреса ячеек. Существуют два основных типа ссылок: относительные и абсолютные. Разли­чия между относительными и абсолютными ссылками проявля­ются при копировании формулы из активной ячейки в другую ячейку. Относительная ссылка в формуле используется для указа­ния адреса ячейки, вычисляемого относительно ячейки, в кото­рой находится формула. При перемещении или копировании фор­мулы из активной ячейки относительные ссылки автоматически обновляются в зависимости от нового положения формулы. Отно­сительные ссылки имеют следующий вид: Al, B3. По умолчанию при наборе формул в Excel используются относительные ссылки. Абсолютная ссылка в формуле используется для указания фик­сированного адреса ячейки.

При перемещении или копировании формулы абсолютные ссыл­ки не изменяются. В абсолютных ссылках перед неизменяемым зна­чением адреса ячейки ставится знак доллара (например: $А$1).

Рассмотрим действие относительных и абсолютных ссылок на Примере. Пусть нам необходимо вычислить стоимость комплекту­ющих для компьютера в рублях, если известны их цены в долла­рах и курс рубля. Для вычисления цены в рублях необходимо ум­ножить цену в долларах на курс доллара. Ниже приведена техно­логия работы (рис. 5.16, а). 1 1. Ввести в ячейки А5, А6 и А7 названия устройств, а в ячейки,В5, В6, В7 — их цены в долларах.

2. Ввести в ячейку С2 курс рубля.

3. Ввести в ячейку С5 формулу =В5*$С$2, где В5 — относитель­ная ссылка, а $С$2 — абсолютная.

4. Скопировать формулы в ячейки С6 и С7; абсолютная ссылка На ячейку $С$2 останется неизменной, а относительная В5 изме­няется на величину смещения от активной ячейки.

Рис. 5.16. Технология работы с данными:

а — использование относительных и абсолютных ссылок; б — выделение диапа­зона ячеек; в — автосуммирование

Если символ доллара стоит перед буквой (например: $А1), то координата столбца абсолютная, а строки — относительная. Если символ доллара стоит перед числом (например: А$1), то, наобо­рот, координата столбца относительная, а строки — абсолютная. Такие ссылки называются смешанными.

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

5.4.3. Табличные вычисления

Возможность использования формул и функций является од­ним из важнейших свойств табличного процессора. Это, в частно­сти, позволяет проводить статистический анализ числовых значе­ний в таблице. В качестве аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: «+» (сложение); ***Т» (вычитание); «*» (умножение); «/» (деление).

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

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

Предположим, что в ячейке А1 таблицы находится число 100,

а в ячейке В1 — число 20. Чтобы разделить первое число на второе и результат поместить в ячейку С1, в ячейку С1 следует ввести соответствующую формулу (=А1/В1) и нажать клавишу [Enter]. Ввод формул можно существенно упростить, используя маленький трюк. После ввода знака равенства следует просто щелкнуть мышью по 'первой ячейке, затем ввести операцию деления и щелкнуть по •второй ячейке (рис. 5.17, а).

Диапазон — это группа выбранных ячеек. Для выбора прямо­угольной области необходимо перевести указатель мыши в один f из углов выбираемой области и, удерживая левую кнопку мыши, протянуть указатель в противоположный угол области (рис. 5.17, б). После отпускания кнопки мыши все ячейки, кроме первой, выделяются инверсным цветом. Для выбора целых строк и столбцов. Можно использовать маркеры строк и столбцов по краям рабочей Области. Щелчок на кнопке в верхнем левом углу области позволяет выбрать весь рабочий лист целиком. Если при выборе ячеек удерживать нажатой клавишу [Ctrl], то можно добавлять новые диапазоны к уже выбранному. Этим приемом можно создавать Несвязанные диапазоны.

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

Однако жесткие табличные структуры вносят свои ограниче­ния и дополнительные особенности.

Нажатие клавиши [Del] приводит к удалению содержимого ^Выбранных ячеек. Удаление самих ячеек сопровождается измене­нием структуры таблицы, т. е. происходит смещение ячеек, новые ячейки становятся на место удаленных.

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

Рис. 5.17. Использование Мастера функций:

а — автоматизация ввода данных; б — диалоговое окно Мастера функций: шаг /; в — диалоговое окно Мастера функций: шаг 2

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

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

Формулы могут состоять не только из арифметических операторов и адресов ячеек. Часто в вычислениях приходится использовать формулы, содержащие функции. Excel имеет несколько сотен лекций, которые подразделяются на следующие группы: «Математические», «Логические», «Статистические, «Финансовые», «Дата и время» и т.д. Рассмотрим на примерах работу с математическими и логическими функциями.

Математические функции. Одной из наиболее часто ис­пользуемых операций является суммирование значении диапазо­на ячеек для расчета итоговых результатов. На панели инструмен­тов Стандартная расположена кнопка® (Автосуммирование), ко­торая используется для автоматического суммирования чисел с помощью функции СУММ.

Пример 1. Таблица содержит цены на комплектующие компью­тера. Вычислить их суммарную стоимость (рис. 5.16, в). Произвести суммирование значений диапазона ячеек.:

1. Выделить ячейку С8, в которую следует поместить сумму.

2. Нажать кнопку ®, после чего будет выделен диапазон ячеек

для суммирования СУММ (С5:С7).

3 Если предложенный диапазон не подходит, перетащить ука­затель по ячейкам, которые нужно просуммировать. Нажать кла­вишу [Enter].

Пример 2. Составить таблицу значений функции у = х³ на от­резке [-3; 3] с шагом 1.

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

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

2. В окне диалога Мастер функции: шаг 1 в списке «Категории» выбрать вариант «Математические», а в списке «Функции» выбрать вариант «Степень». Нажать кнопку ОК (рис. 5.17, б).

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

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

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

Аргументами логических функций являются логические значе­ния ИСТИНА (1) или ЛОЖЬ (0). Логические значения, в свою очередь, могут быть получены как результат определения значе­ния логических выражений. Например, для логического выраже­ния 10 > 5 результатом будет логическое значение ИСТИНА, а для логического выражения А1 < А2 (где в ячейке А1 хранится число 10, а в ячейке А2 — число 5) — логическое значение ЛОЖЬ.

Логическая функция ИЛИ имеет в качестве аргументов логиче­ские значения, которые могут быть истинными или ложными, и задается формулой =И(лог_знач 1; лог_знач2;...). Функция прини­мает значение ИСТИНА только тогда, когда все аргументы имеют значение ИСТИНА. Например, значение функции = И (10 > 5; 10 < 5) - ЛОЖЬ.

Логическая функция ИЛИ имеет в качестве аргументов ло­гические значения и задается формулой = ИЛИ (лог_знач1; лог_знач2;...). Функция принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Например, зна­чение функции = ИЛИ (10 > 5;10< 5) — ИСТИНА.

Логическая функция НЕ меняет значение своего аргумента на противоположное и задается формулой = НЕ (лог_знач1). Функ­ция принимает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и наоборот. Например, значение функции = НЕ (10 > 5) — ЛОЖЬ.

Построим с помощью электронных таблиц таблицу истинно­сти операции логического умножения, используя логическую функцию И.

Пример 3. Построение таблицы истинности логических опера­ций (рис. 5.18).

1. В пары ячеек (А2, В2), (A3, ВЗ), (А4, В4) и (А5, В5) ввести пары значений аргументов логической операции (0,0), (0,1), (1,0) и (U).

Рис. 5.18. Пример использования логических функций

2.В ячейку С2 ввести формулу логического умножения: =И(А2;В2).

3.Скопировать формулу в ячейки СЗ, С4 и С5.

4.Значением этой функции в трех случаях является ЛОЖЬ и только в последнем — ИСТИНА.

Повторите шаги 2, 3 для операций логического сложения и отрицания.

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

»

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

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

• числа сортируются от наименьшего отрицательного до наи­большего положительного числа;

• текст сортируется в следующем порядке: числа, знаки, ла­тинский алфавит, русский алфавит;

• логическое значение ЛОЖЬ предшествует логическому зна­чению ИСТИНА;

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

Можно проводить вложенные сортировки, т. е. сортировать дан­ные по нескольким столбцам; при этом задается последователь­ность сортировки столбцов. Если в столбце, по которому осуществляется сортировка, имеются одинаковые значения, то можно задать сортировку по второму столбцу (вложенную сортировку).

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

На рис. 5.19, а представлена таблица цен на комплектующие компьютера. На этом примере рассмотрим операцию упорядоче­ния данных по столбцу, содержащему цену устройств.

Выделить одну из ячеек с данными и ввести команду Данные/ Сортировка.

На диалоговой панели Сортировка диапазона (рис. 5.19, б) в списке Сортировать по выбрать название нужного столбца (в дан­ном случае Цена) и установить переключатель в положении по возрастанию.

После нажатия кнопки ОК мы получим отсортированную таб­лицу, в которой устройства расположены в порядке возрастания их цены (рис. 5.19, в).

Перед выполнением вложенных сортировок необходимо пра­вильно задать последовательность сортировки столбцов.

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

Рис. 5.20. Автофильтр:

А - диалоговое окно Автофильтр; б — диалоговое окно Пользовательский авто­фильтр; в — результат применения Автофильтра

Например, мы хотим найти все строки, которые содержат ин формацию об устройствах стоимостью более 100 у. е.

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

Развернуть список в столбце «Цена» и выбрать пункт «Усло­вие...» для ввода пользовательских условий (рис. 5.20, а).

На диалоговой панели Пользовательский автофильтр в полях ввести оператор условия поиска «больше» и значение 100 (рис 5.20, б).

В результате будут найдены две строки, удовлетворяющие за­данному фильтру (рис. 5.20, в).