Лабораторная работа №3
ВЫЧИСЛЕНИЯ В MS EXCEL
Цель работы: создание и копирование формул; порядок операций в формулах; функции; изучение способов ссылок на ячейки в Microsoft Excel.
ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Общие понятия о вычислениях в Microsoft Excel
Все вычисления в Microsoft Excel выполняются с помощью формул, которые можно вводить в любые ячейки листа. Если содержимое ячейки начинается со знака равенства (=), Excel полагает, что вслед за этим знаком идет формула, и пытается выполнить указанную операцию. Когда это удается, в ячейку с такой формулой выводится результат расчета. Если что-то не так, появляется сообщение об ошибке.
Сама формула отображается в ячейке только в том случае, когда ячейка находится в режиме редактирования текста (т.е. после двойного щелчка на ней). Если ячейка просто выделена, то ее формула выводится в строке формулы в верхней части окна Excel.
Формула также может включать следующие элементы:
- константы;
- операторы;
- ссылки;
- функции.
1.1.1 Константы. Константа представляет собой готовое (не вычисляемое) значение. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. Выражение и его значение константами не являются. Если в формуле в ячейке не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.
1.1.2 Операторы. Оператор – это знак или символ, задающий тип вычисления в выражении. Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов:
- арифметические;
- текстовые;
- операторы сравнения;
- операторы ссылок.
Арифметические операторы. Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. В табл. 1.1 представлены арифметические операторы, используемые в Microsoft Excel.
Таблица 1.1 – Арифметические операторы
| Арифметический оператор | Значение (пример) |
| + (знак плюс) | Сложение (3+3) |
| – (знак минус) | Вычитание (3–1) Отрицание (–1) |
| * (звездочка) | Умножение (3*3) |
| / (косая черта) | Деление (3/3) |
| % (знак процента) | Процент (20%) |
| ^ (крышка) | Возведение в степень (3^2) |
Операторы сравнения. Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ. В табл. 1.2 представлены операторы сравнения, используемые в Microsoft Excel.
Таблица 1.2 – Операторы сравнения
| Оператор сравнения | Значение (пример) |
| = (знак равенства) | Равно (A1=B1) |
| > (знак больше) | Больше (A1>B1) |
| < (знак меньше) | Меньше (A1<B1) |
| >= (знак больше или равно) | Больше или равно (A1>=B1) |
| <= (знак меньше или равно) | Меньше или равно (A1<=B1) |
| <> (знак не равно) | Не равно (A1<>B1) |
Текстовый оператор конкатенации. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку. Например, "Северный"&"ветер".
Оператор ссылки. Для описания ссылок на диапазоны ячеек используются операторы, представленные в табл. 1.3.
Таблица 1.3 – Оператор ссылки
| Оператор ссылки | Значение (пример) |
| : (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15) |
| ; (точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15)) |
| (пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8) |
1.1.3 Ссылки. Ссылка указывает на ячейку или диапазон ячеек листа и передает в Microsoft Excel сведения о расположении значений или данных, которые требуется использовать в формуле. При помощи ссылок можно использовать в одной формуле данные, находящиеся в разных частях листа, а также использовать в нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями.
Стиль ссылок A1. По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2. Примеры ссылок представлены в табл. 1.4.
Таблица 1.4 – Примеры ссылок стиля А1
| Ячейка или диапазон | Использование |
| Ячейку в столбце A и строке 10 | A10 |
| Диапазон ячеек: столбец А, строки 10-20. | A10:A20 |
| Диапазон ячеек: строка 15, столбцы B-E. | B15:E15 |
| Все ячейки в строке 5. | 5:5 |
| Все ячейки в строках с 5 по 10. | 5:10 |
| Все ячейки в столбце H. | H:H |
| Все ячейки в столбцах с H по J. | H:J |
| Диапазон ячеек: столбцы А-E, строки 10-20. | A10:E20 |
Ссылка на другой лист. В примере =СРЗНАЧ(Маркетинг!B1:B10) функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же самой книге.
Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.
Относительные ссылки. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки.
Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр.
Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется
Стиль трехмерных ссылок. Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.
Трехмерные ссылки нельзя использовать в формулах массива. Формула массива – это формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш Ctrl+Shift+Enter.
Трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел), а также в формулах, использующих неявное пересечение. Неявное пересечение – это ссылка на диапазон ячеек вместо одной ячейки, из которого в расчете выбирается одна соответствующая ячейка. Например, если в ячейке C10 содержится формула =B5:B15*5, на 5 будет умножено значение из ячейки B10, потому что ячейки B10 и C10 находятся в одной строке.
Для присвоения имени ячейкам на нескольких листах (создания трехмерной ссылкой) необходимо:
1) выберите команду меню Вставка Þ Имя Þ Присвоить;
2) введите имя в диалоговом окне Присвоить имя;
3) если в поле Формула содержится ссылка, выделите ссылку вместе со знаком равенства (=) и нажмите клавишу Backspace;
4) введите знак равенства (=) в поле Формула;
5) выберите ярлычок первого листа, на который нужно сослаться, и, удерживая нажатой клавишу Shift, выберите ярлычок последнего листа;
6) выделите ячейку или диапазон ячеек, на которые необходимо сослаться.
Стиль ссылок R1C1. Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца (табл. 1.5).
Таблица 1.5 – Примеры ссылок стиля R1C1
| Ссылка | Значение |
| R[-2]C | Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце |
| R[2]C[2] | Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее |
| R2C2 | Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце |
| R[-1] | Относительная ссылка на строку, расположенную выше текущей ячейки |
| R | Абсолютная ссылка на текущую строку |
При записи макроса Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Excel использует при записи формулы стиль ссылок R1C1, а не A1. Чтобы включить или выключить стиль ссылок R1C1 выберите команду меню Сервис Þ Параметры, перейдите на вкладку Общие и установите или снимите флажок Стиль ссылок R1C1.
1.1.4 Функции. Функции – это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10. Различают следующие категории функций:
- Функции баз данных. В Excel включены функции рабочего листа, используемые для анализа данных из списков или БД. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией.
- Функции даты и времени (табл. 1.6).
Таблица 1.6 – Функции даты и времени
| Функция | Назначение |
| ДАТА | Возвращает заданную дату в числовом формате Microsoft Excel. |
| ДАТАЗНАЧ | Преобразует дату из текстового формата в числовой. |
| ДЕНЬ | Преобразует дату в числовом формате в день месяца. |
| ДНЕЙ360 | Вычисляет количество дней между двумя датами на основе 360-дневного года. |
| ДАТАМЕС | Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты. |
| КОНМЕСЯЦА | Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев. |
| ЧАС | Преобразует дату в числовом формате в часы. |
| МИНУТЫ | Преобразует дату в числовом формате в минуты. |
| МЕСЯЦ | Преобразует дату в числовом формате в месяцы. |
| ЧИСТРАБДНИ | Возвращает количество рабочих дней между двумя датами. |
| ТДАТА | Возвращает текущую дату и время в числовом формате. |
| СЕКУНДЫ | Преобразует дату в числовом формате в секунды. |
| ВРЕМЯ | Возвращает заданное время в числовом формате. |
| ВРЕМЗНАЧ | Преобразует время из текстового формата в числовой формат. |
| СЕГОДНЯ | Возвращает числовой формат текущей даты. |
| ДЕНЬНЕД | Преобразует дату в числовом формате в день недели. |
| НОМНЕДЕЛИ | Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата. |
| РАБДЕНЬ | Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней. |
| ГОД | Преобразует дату в числовом формате в год. |
| ДОЛЯГОДА | Возвращает долю года, которую составляет количество дней между начальной и конечной датами. |
- Внешние функции. Эти функции загружаются в виде надстроек. Надстройка – вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.
- Инженерные функции.
- Финансовые функции.
- Информационные функции (табл. 1.7).
Таблица 1.7 – Информационные функции
| Функция | Назначение |
| ЯЧЕЙКА | Возвращает информацию о формате, местоположении или содержимом ячейки. |
| ТИП.ОШИБКИ | Возвращает номер, соответствующий одному из типов ошибок Excel. |
| ИНФОРМ | Возвращает информацию о текущей операционной среде. |
| ЕПУСТО | Возвращает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку. |
| ЕОШ | Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д. |
| ЕОШИБКА | Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки. |
| ЕЧЁТН | Возвращает логическое значение ИСТИНА, если аргумент — четное число. |
| ЕЛОГИЧ | Возвращает логическое значение ИСТИНА, если аргумент ссылается на логическое значение. |
| ЕНД | Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д (значение недоступно). |
| ЕНЕТЕКСТ | Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом. |
| ЕЧИСЛО | Возвращает логическое значение ИСТИНА, если аргумент ссылается на число. |
| ЕНЕЧЁТ | Возвращает логическое значение ИСТИНА, если аргумент — нечетное число. |
| ЕССЫЛКА | Возвращает логическое значение ИСТИНА, если аргумент ссылается на ссылку. |
| ЕТЕКСТ | Возвращает логическое значение ИСТИНА, если аргумент ссылается на текст. |
| Ч | Возвращает значение, преобразованное в число. |
| НД | Возвращает значение ошибки #Н/Д. |
| ТИП | Возвращает тип значения. |
- Логические функции (табл. 1.8).
Таблица 1.8 – Логические функции
| Функция | Назначение |
| И | Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА. |
| ЛОЖЬ | Возвращает логическое значение ЛОЖЬ. |
| ЕСЛИ | Выполняет проверку условия. |
| НЕ | Меняет на противоположное логическое значение своего аргумента. |
| ИЛИ | Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА. |
| ИСТИНА | Возвращает логическое значение ИСТИНА. |
- Функции ссылки и автоподстановки.
- Арифметические и тригонометрические функции (табл. 1.9).
Таблица 1.9 – Некоторые арифметические и тригонометрические функции
| Функция | Назначение |
| ABS | Возвращает модуль (абсолютную величину) числа. |
| ACOS | Возвращает арккосинус числа. |
| ACOSH | Возвращает гиперболический арккосинус числа. |
| ASIN | Возвращает арксинус числа. |
| ASINH | Возвращает гиперболический арксинус числа. |
| ATAN | Возвращает арктангенс числа. |
| ATAN2 | Возвращает арктангенс для заданных координат x и y. |
| ATANH | Возвращает гиперболический арктангенс числа. |
| COS | Возвращает косинус числа. |
| COSH | Возвращает гиперболический косинус числа. |
| ГРАДУСЫ | Преобразует радианы в градусы. |
| ЧЁТН | Округляет число до ближайшего четного целого. |
| EXP | Возвращает число e, возведенное в указанную степень. |
| ФАКТР | Возвращает факториал числа. |
| ОКРВНИЗ | Округляет число до ближайшего меньшего по модулю целого. |
| НОД | Возвращает наибольший общий делитель. |
| ЦЕЛОЕ | Округляет число до ближайшего меньшего целого. |
| НОК | Возвращает наименьшее общее кратное. |
| LN | Возвращает натуральный логарифм числа. |
| LOG | Возвращает логарифм числа по заданному основанию. |
| LOG10 | Возвращает десятичный логарифм числа. |
| МОПРЕД | Возвращает определитель матрицы, хранящейся в массиве. |
| МОБР | Возвращает обратную матрицу (матрица хранится в массиве). |
| МУМНОЖ | Возвращает произведение матриц, хранящихся в массивах. |
| ОСТАТ | Возвращает остаток от деления. |
| ОКРУГЛТ | Возвращает число, округленное с требуемой точностью. |
| НЕЧЁТ | Округляет число до ближайшего нечетного целого. |
| ПИ | Возвращает число «пи». |
| СТЕПЕНЬ | Возвращает результат возведения числа в степень. |
| ПРОИЗВЕД | Возвращает произведение аргументов. |
| ЧАСТНОЕ | Возвращает целую часть частного при делении. |
| РАДИАНЫ | Преобразует градусы в радианы. |
| СЛЧИС | Возвращает случайное число в интервале от 0 до 1. |
| СЛУЧМЕЖДУ | Возвращает случайное число в заданном интервале. |
| ОКРУГЛ | Округляет число до указанного количества десятичных разрядов. |
| ОКРУГЛВНИЗ | Округляет число до ближайшего меньшего по модулю целого. |
| ОКРУГЛВВЕРХ | Округляет число до ближайшего по модулю большего целого. |
| ЗНАК | Возвращает знак числа. |
| SIN | Возвращает синус заданного угла. |
| SINH | Возвращает гиперболический синус числа. |
| КОРЕНЬ | Возвращает положительное значение квадратного корня. |
| СУММ | Суммирует аргументы. |
| TAN | Возвращает тангенс числа. |
| TANH | Возвращает гиперболический тангенс числа. |
| ОТБР | Отбрасывает дробную часть числа. |
- Статистические функции.
- Текстовые функции (табл. 1.10).
Таблица 1.10 – Некоторые текстовые функции
| Функция | Назначение |
| ASC | Преобразует полноширинные (двухбайтные) английские буквы или знаки катакана в текстовой строке в полуширинные (один байт). |
| БАТТЕКСТ | Преобразует число в текст, используя денежный формат Я (БАТ). |
| СИМВОЛ | Возвращает знак с заданным кодом. |
| ПЕЧСИМВ | Удаляет все непечатаемые знаки из текста. |
| КОДСИМВ | Возвращает числовой код первого знака в текстовой строке. |
| СЦЕПИТЬ | Объединяет несколько текстовых элементов в один. |
| РУБЛЬ | Преобразует число в текст, используя денежный формат доллара. |
| СОВПАД | Проверяет идентичность двух текстов. |
| НАЙТИ | Ищет вхождение одного текста в другой (с учетом регистра). |
| ФИКСИРОВАННЫЙ | Форматирует число и преобразует его в текст с заданным числом десятичных знаков. |
| JIS | Преобразует полуширинные (однобайтные) английские буквы или знаки катакана в текстовой строке в полноширинные (два байта). |
| ЛЕВСИМВ | Возвращает самые левые знаки текстового значения. |
| ДЛСТР | Возвращает количество знаков в текстовой строке. |
| СТРОЧН | Делает все буквы в тексте строчными. |
| ПСТР | Возвращает определенное число знаков из строки текста, начиная с указанной позиции. |
| PHONETIC | Извлекает фонетические (фуригана) знаки из текстовой строки. |
| ПРОПНАЧ | Делает прописной первую букву в каждом слове текста. |
| ЗАМЕНИТЬ | Заменяет знаки в тексте. |
| ПОВТОР | Повторяет текст заданное число раз. |
| ПРАВСИМВ | Возвращает самые правые знаки текстовой строки. |
| ПОИСК | Ищет вхождение одного текста в другой (без учета регистра). |
| ПОДСТАВИТЬ | Заменяет в текстовой строке старый текст новым. |
| Т | Преобразует аргумент в текст. |
| ТЕКСТ | Форматирует число и преобразует его в текст. |
| СЖПРОБЕЛЫ | Удаляет из текста пробелы. |
| ПРОПИСН | Делает все буквы в тексте прописными. |
| ЗНАЧЕН | Преобразует текстовый аргумент в число. |
