Основные сведения о программе MS Excel

Лабораторная работа №5

 

 

Тема: Основы статистических методов исследования в химии и фармации с использованием редактора электронных таблиц MS Excel.

 

Цель работы:Приобретение навыков обработки и обобщения химико-фармацевтических данных, а также навыков работы со статистическими функциями Excel.

Теоретические сведения

Основные сведения о программе MS Excel

MS Excel – программа для расчётов с помощью электронных таблиц. Данная программа работает с таблицами, состоящими из ячеек (полей), упорядоченных по строкам и столбцам (колонкам), в которых может содержаться разнообразная информация. Основным объектом, с которым работает редактор, является рабочая книга. Рабочая книга состоит из листов. Количество листов в книге ограничено объемом доступной оперативной памяти (по умолчанию 3 листа). Рабочее пространство листа представлено в виде таблицы, состоящей из столбцов и строк. Каждому столбцу присвоено название в виде латинских букв или их сочетаний (A, B,..Z, AB…AZ). Каждой строке присвоен номер от 1 до максимального значения (в разных версиях - различно). На пересечении строк и столбцов расположены ячейки. Максимальный размер листа в версии 2010 равен 1 048 576 строк и 16 384 столбца. Адресация к ячейкам осуществляется путём указания заголовка столбца и строки. Например, адрес строки, находящейся в столбце М и строке 15 будет иметь вид: М15.

Диапазон – это набор из двух или более ячеек рабочего листа, которые могут быть как смежные, так и несмежные. Адрес диапазона задаётся адресами левой верхней ячейки диапазона и правой нижней ячейки, разделённых знаком «:» (двоеточие). Если же диапазон имеет произвольную форму, то его адрес задаётся адресами составляющих его прямоугольных поддиапазонов, разделённых знаками «;» (точка с запятой). К примеру, «B6:D10» или «H7:J8; G10:I12». Диапазоны в Excel очень важны, так как многие операции, такие как копирование, перемещение, форматирование и пр., могут выполняться не только с отдельными ячейками, но и с диапазонами. Выделением называется маркировка одной или нескольких ячеек с целью дальнейшего выполнения операций над ними. Выделенная область отличается другим цветом ячеек. Для выделения диапазона достаточно позиционировать указатель мыши над угловой ячейкой (не имеет значения, какого угла) выделяемого диапазона и при нажатой левой кнопке мыши переместить его к противоположной (по диагонали) ячейке этого диапазона. Чтобы выделить диапазон произвольного вида, необходимо при нажатой клавише Ctrl выделять при помощи мыши (как было описано выше) прямоугольные диапазоны и отдельные ячейки, из которых будет состоять этот диапазон. Формулы – это основа вычислений с помощью MS Excel. Любая формула в электронных таблицах начинается со знака равенства «=». Если не ввести знак равенства, то вводимые данные будут интерпретироваться программой как текст или число. После ввода формулы, в строке формул будет отражаться выражение формулы, а в ячейке – результат. Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок. В табл. 1 представлены знаки операций, используемые в формулах MS Excel.

Табл. 1. – Знаки операций.

Вид операций	Знак
Арифметические	Знак плюс (+)
Знак минус (-)
Знак умножения (*)
Знак деления (/)
Знак возведения в степень (^)
Процент (%)
Текстовые	Конкатенация (&)
Сравнение	Равно (=)
Меньше (<)
Меньше или равно (<=)
Больше (>)
Больше или равно (>=)
Не равно (<>)
Адресные	Двоеточие (;)
Точка с запятой (;)
Пробел ( )

 

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

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

Ссылки бывают нескольких типов:

1. Относительные. Ссылки такого вида определяют адреса ячеек по отношению к активной ячейке, например «A1»;

2. Абсолютные. Эти ссылки задают адреса ячеек в соответствие с их положением на рабочем листе, например «$A$1» (здесь для фиксации строки и столбца используется знак «$»);

3. Смешанные. У таких ссылок есть как абсолютная, так и относительная часть адреса, например «A$1» или «$A1».

Тип имеющейся ссылки можно быстро изменять при помощи клавиши [F4]. Различие между относительными и абсолютными ссылками существенно, особенно при копировании формул в пределах рабочего листа. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Ссылки на ячейки других рабочих листов активной рабочей книги вводятся аналогично ссылкам в пределах текущего рабочего листа. Название рабочего листа отделяется от адреса ячейки восклицательным знаком, например, «Лист2!A1». По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы.

В редакторе электронных таблиц есть возможность вводить трёхмерные ссылки. Трехмерные ссылки используются при необходимости выполнения действий с данными из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Для того чтобы ввести трёхмерную ссылку, нужно выделить область ячеек на первом листе, нажмите клавишу [Shift], и не отпуская её, укажите последний лист трёхмерного диапазона. Трехмерные ссылки обычно используются для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА. Например, «=СУММ(Лист1:Лист3!C1:C16)».

Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы (Стиль ссылок R1C1). Этот стиль полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца. К примеру, R[-2]C - относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце, R[2]C[2] - относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее, R2C2 - абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце, R[-1] -относительная ссылка на строку, расположенную выше текущей ячейки, R - абсолютная ссылка на текущую строку.

Функции Excel - это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Их можно сравнить со специальными клавишами на калькуляторах, предназначенных для вычисления квадратных корней, логарифмов и проч. Excel имеет несколько соте н встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно. Функции состоят из двух частей: имени функциии одного или нескольких аргументов.Имя функции, например, СУММ, - описывает операцию, которую эта функция выполняет. Аргументызадают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.

=СУММ(В1:В5)

Аргумент функциизаключен в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции. В случае ввода пробела или другого символа между именем и открывающей скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:

= ПИ()

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает, что необходимо перемножить числа в ячейках А1, А3, А6:

=ПРОИЗВЕД(А1;А3;А6)

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

=СУММ(А2:А5;В4:В8)

Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций. В приведенных ранее примерах все аргументы были ссылками на ячейки или диапазоны. Но в качестве аргументов можно также использовать числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов, и их в дальнейшем можно использовать в качестве аргументов в других функциях. Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 24, 987, 49:

=СУММ(24;987;49)