 |
Очистка, вставка и удаление на рабочем листе
|
|
|
|
Задание №2
Использование формул и функций для вычислений в MS Excel
Теоретический материал
Формулы в Excel.
Обработка данных, хранимых в ячейках рабочих листов Excel, осуществляется по формулам, определенным пользователем. Для перехода в режим создания формул необходимо выделить ячейку и ввести знак =. В формулах могут использоваться как стандартные арифметические операторы, так и встроенные функции Excel. Пример определения простейшей формулы: = (7 – 4) * 2.
При вычислении математических выражений по формуле Excel руководствуются следующими традиционными правилами, определяющими приоритет выполнения операций:
· В первую очередь вычисляются выражения внутри круглых скобок;
· Определяются значения, возвращаемые встроенными функциями;
· Выполняются операции возведения в степень (^), затем умножения (*) и деления (/), а после – сложения (+) и вычитания (–).
Замечание. Необходимо отметить, что операции с одинаковым приоритетом выполняются слева направо.
Функции MS Excel. Арифметические и тригонометрические функции.
В Excel имеется целый ряд встроенных математических функций, существенно облегчающих решение задач. Синтаксис функций:
<Имя Функции><Аргумент 1; …; Аргумент N>.
Здесь в качестве аргумента могут использоваться как непосредственное значение, так и идентификатор ячейки.
1) При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой: =ПРОИЗВЕД(A1;B2;C4). Эта формула указывает Excel, что необходимо перемножить числа в ячейках A1, B2 и C4.
2) Любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция =ПРОИЗВЕД(A1:A3;B2:B4) имеет два аргумента, но перемножает содержимое шести ячеек.
|
|
|
3) Аргументы не обязательно должны образовывать непрерывные диапазоны ячеек: =ПРОИЗВЕД(A1:A3;B2;B4:B7).
4) Некоторые функции, например ПИ(), не имеют аргументов. Комбинацию функций можно использовать для создания выражения, например: СУММ(КОРЕНЬ(16);COS(A1*ПИ())).
Перечень встроенных математических функций из категории Арифметические и тригонометрические приведен в табл. 1 – 5.
Таблица 1. Основные математические функции
| № | Наименование | Обозначение | Применение |
| Знак | =ЗНАК(x) | x – число, ссылка на ячейку с числом или формула, возвращающая числовое значение. Возвращаемые значения: 1, если x > 0; 0, если x = 0; –1, если x < 0. | |
| Абсолютное значение | =ABS(x) | x – число, ссылка на ячейку с числом или формула, возвращающая числовое значение. | |
| Сумма | =СУММ(x1;…xn) | n<=30; игнорируются пустые ячейки, текстовые и логические значения. | |
| Произведение | =ПРОИЗВЕД(x1;…xn) | ||
| Корень квадратный | =КОРЕНЬ(x) | x >= 0. | |
| Корень квадратный из x * π | =КОРЕНЬПИ(x) | Возвращает квадратный корень из числа (x * π). | |
| Факториал | =ФАКТР(x) | x>= 0. Если x нецелое, то дробная часть отбрасывается перед вычислением функции. | |
| Частное | =ЧАСТНОЕ(x;y) | Возвращает целую часть от деления [ x/y ] | |
| Остаток от деления | =ОСТАТ(x;y) | Возвращает остаток от деления, вычисляемый как x – ent[ x/y ]. Если x < y, то функция возвращает x. | |
| Наименьшее общее кратное | =НОК(x1;…xn) | n <=29. Если аргумент xi не целый, то он усекается до целого. Ограничения на аргументы: xi > 0. | |
| Наибольший общий делитель | =НОД(x1;…xn) |
Таблица 2. Логарифмические функции
| № | Наименование | Обозначение | Применение |
| Натуральный логарифм | =LN(x) | x > 0, при x <= 0 возвращается ошибочное значение #ЧИСЛО! | |
| Десятичный логарифм | =LOG10(x) | x > 0, при x <= 0 возвращается ошибочное значение #ЧИСЛО! | |
| Логарифм по заданному основанию | =LOG(x;Основание) | x > 0, при x <= 0 возвращается ошибочное значение #ЧИСЛО! По умолчанию = 10 | |
| Экспонента от x | =EXP(x) | ex | |
| Возведение в степень | =СТЕПЕНЬ(x;a) | xa |
|
|
|
Таблица 3. Тригонометрические функции
| № | Математическое обозначение | Обозначение в Excel | Применение |
| π | =ПИ() | Возвращает значение π с 14 значащими разрядами после десятичной точки | |
| =ГРАДУСЫ(угол) | Преобразует угол в радианах в градусы | ||
| =РАДИАНЫ(угол) | Преобразует угол в градусах в радианы | ||
| Sin x | =SIN(x) | x –угол в радианах | |
| Cos x | =COS(x) | x –угол в радианах | |
| Tg x | =TAN(x) | x –угол в радианах | |
| Arctg x | =ATAN(x) | Возвращаемое значение лежит на интервале между – π / 2 и π / 2 радиан | |
| Arcsin x | =ASIN(x) | Ограничения на аргумент: –1<= x <= 1. Возвращаемое значение лежит на интервале между – π / 2 и π / 2 радиан. | |
| Arccos x | =ACOS(x) | Ограничения на аргумент: –1<= x <= 1. Возвращаемое значение лежит на интервале от 0 до πрадиан. | |
| Arctg x,y | =ATAN2(x;y) | Возвращает значение угла между осью x и линией, соединяющей точки с координатами (0,0) и (x, y). Возвращаемое значение z лежит в диапазоне – π < z < π, исключая – π. Один из аргументов x или y может быть равен 0; если нулю равны оба аргумента, то функция возвращает ошибочное значение #ДЕЛ/0! |
Примечание: x – значение угла в радианах.
В Excel встроены также гиперболические функции: =SINH(x), =COSH(x), =TANH(x), =ASINH(x), =ACOSH(x), =ATANH(x).
Таблица 5. Функции генерирования случайных чисел
| № | Обозначение | Применение |
| =СЛЧИС() | Генерирует случайные числа, распределенные на интервале [0;1]. Значение, возвращаемое функцией, изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции изменяется каждый раз при вводе данных в листе. | |
| =СЛУЧМЕЖДУ(x;y) | Генерирует случайные числа, равномерно распределенные на интервале [ x;y ]. |
Таблица 4. Функции округления
| № | Наименование | Обозначение | Применение |
| Округление | =ОКРУГЛ(x;n) | n = 0 – округление до ближайшего целого; n > 0 – округление дробной части x до n разрядов после десятичной точки (n = –2, округление до сотых долей); n < 0 – округление до n разрядов слева от десятичной запятой (n = 2, округление до сотен). Цифры < 5 округляются с недостатком (вниз), цифры >= 5 округляются с избытком (вверх). | |
| Округление с избытком | =ОКРУГЛВВЕРХ(x;n) | Функция аналогична функции ОКРУГЛ, но округление до ближайшего большего | |
| Округление с недостатком | =ОКРУГЛВНИЗ(x;n) | Функция аналогична функции ОКРУГЛ, но округление до ближайшего меньшего | |
| Округление до четного | =ЧЁТН(x) | Округление вверх до ближайшего четного целого числа. x > 0 – округление вверх; x > 0 – округление вниз; | |
| Округление до нечетного | =НЕЧЁТН(x) | Округление вверх до ближайшего нечетного целого числа. x > 0 – округление вверх; x > 0 – округление вниз; | |
| Округление до большого числа, кратного n | =ОКРВВЕРХ(x;n) | Округление вверх до ближайшего большего целого числа, кратного n. Значения x и n должны иметь одинаковый знак. В противном случае возвращается ошибочное значение #ЧИСЛО! | |
| Округление до меньшего числа, кратного n | =ОКРВНИЗ(x;n) | Округление вверх до ближайшего меньшего целого числа, кратного n. Значения x и n должны иметь одинаковый знак. В противном случае возвращается ошибочное значение #ЧИСЛО! | |
| Округление до целого | =ЦЕЛОЕ(x) | Округление числа x до ближайшего целого числа. | |
| Отбрасывание разрядов | =ОТБР(x,n) | Отбрасывает все цифры справа от десятичной точки. Аргумент n определяет позицию в дробной части числа, после которого производится усечение. |
|
|
|
Ввод функций
Функции могут вводиться в рабочий лист несколькими способами. После ввода знака = функция либо может быть введена непосредственно с клавиатуры, либо выбираться в поле имени (см. рис.1), которое в этом случае становится полем функции.
Рис.1. Выбор функции через поле функции
Существует два способа, равноценных последнему, но не требующих предварительного ввода знака равенства:
§ через пункт меню Вставка | Функция (Office 2003);
§ с помощью кнопки Вставка функции 
;
§ на вкладке Формулы с помощью кнопки Вставить функцию (Office 2007,2010).
Функция определяется за два шага. На первом шаге в открывшемся окне диалога Мастер функции необходимо сначала выбрать категорию в списке Категория, а затем в алфавитном списке Функция выделить необходимую функцию. На втором шаге задаются аргументы функции. Второе окно диалога Мастер функции содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, то окно диалога увеличивается при вводе дополнительных аргументов. После задания аргументов необходимо нажать кнопку ОК или клавишу Enter.
|
|
|
Ссылки в Excel.
Ссылки в пределах рабочего листа
Данные для вычисления по формуле могут непосредственно вводиться в формулу: = 2 + 3, а также считываться из других ячеек. Для доступа к данным в других ячейках рабочего листа используются ссылки. Ссылка является идентификатором ячейки или группы ячеек в книге.
В Excel различают ссылки трех типов: относительные, абсолютные, смешанные. Существуют два стиля оформления ссылок: стиль A1, или основной, и стиль R1C1.
Ссылки в стиле A1
Относительная ссылка. При рассмотрении механизма относительных ссылок необходимо различать отображаемое и хранимое значения.
Хранимое значение относительной ссылки представляет собой смещение по столбцам и строкам от ячейки с формулой до адресуемой ячейки данных. Поэтому хранимое значение относительной ссылки не зависит от места расположения ячейки с формулой на листе и не изменяется при копировании и перенесении формул.
Отображаемое значение относительной ссылки представляет собой комбинацию из имени столбца и номера ячейки, соответствующую её текущим координатам. Отображаемое значение относительной ссылки автоматически изменяется при копировании формулы в новую ячейку.
Например, в формулах =A1+B1 и =A5+B5, находящихся в ячейках B3 и B7 (рис.2, а), отображаемым значениям A1 и A5 соответствуют одинаковые хранимые значения: < текущий столбец – 1 > < текущая строка – 2 >.
А б
Рис. 2. Ссылки: а – относительная, б – абсолютная
Если до момента фиксации ввода формулы нажимать на клавишу F4, можно изменить ссылку либо на абсолютную, либо на смешанную.
Абсолютная ссылка всегда указывает на фиксированную при создании формулы ячейку или диапазон и не изменяется при переносе или копировании формулы в другую ячейку. Механизм абсолютной адресации включается в двух случаях:
§ при записи знака $ перед именем столбца и номером строки (рис. 2, б);
§ при использовании имени ячейки.
Смешанные ссылки представляют собой комбинацию из относительных и абсолютных ссылок. Можно определить два типа смешанных ссылок.
1. Смешанная ссылка первого типа. В ссылках первого типа символ $ стоит перед буквой, поэтому координата столбца рассматривается как абсолютная, а координата строки – как относительная (рис.3, а).
|
|
|
2. Смешанная ссылка второго типа. В ссылках второго типа символ $ стоит перед числом, поэтому координата столбца рассматривается как относительная, а координата строки – как абсолютная (рис.3, б).
Примеры смешанных ссылок: =$B1+$D7, =B$1+D$7.
Рис.3. Смешанные ссылки
Ссылки в стиле R1C1
При использовании ссылки данного стиля ячейки адресуются по номерам строк (Row) и столбцов (Columm). В частности, ссылка R1C1 означает: строка1, столбец1. Для активизации стиля необходимо выбрать команду Сервис/Параметры и на вкладке Общие установить флажок Стиль ссылок R1C1. Для доступа к данным по умолчанию используются абсолютные ссылки, а при заключении номеров строк и/или столбцов в квадратные скобки включается механизм относительных ссылок.
Например, формула в ячейке R3C2 (рис.4) читается следующим образом: сложить содержимое ячейки, расположенной на две строки ниже и на столбец левее ячейки с формулой, с ячейкой, находящейся на две строки ниже в том же столбце. Формула в ячейке R3C1 использует абсолютные ссылки на ячейки, находящиеся на пересечении первой строки с первым и вторым столбцами.
Примеры: 1. Абсолютные ссылки: R2C6 – ячейка, расположенная во второй строке и шестом столбце; R5C3 – ячейка, расположенная в пятой строке и третьем столбце.
2. Относительные ссылки: RC – ячейка ввода; RC[1] – ячейка справа от ячейки ввода; RC[-2] – ячейка вторая слева от ячейки ввода; R[3]C – ячейка третья снизу от ячейки ввода; R[-1]C – ячейка сверху от ячейки ввода; R[1]C[-1] – ячейка, расположенная на одну строку ниже и на один столбец левее ячейки ввода.
Рис.4. Ссылки в стиле R1C1
Отладка формул
В состав Excel включена совокупность средств, обеспечивающих синтаксический контроль за вводом формул, контроль за правильностью ссылок и типами данными, обрабатываемых с помощью формул.
Для удобств восприятия формулы Excel позволяет разместить ее на нескольких строках в строке формул. Включение в формулу разрыва строки обеспечивается нажатием комбинации клавиш Alt+Enter.
В Excel предусмотрен контроль за вводом формул: при несоответствии количества открывающих и закрывающих скобок выводится сообщение: Ошибка во введенном выражении. Принять предлагаемые исправления? и предлагается свой вариант формулы.
Режим отображения формул
Excel позволяет отображать введенные формулы не только в строке формул, но и в ячейках рабочего листа. Отображение формул обеспечивается установкой флага формулы, доступного через пункт меню Сервис/Параметры…/Вид (Office 2003). Этот режим может быть использован также для документирования работы, если после его установки напечатать лист с содержащими в нем формулами.
Трассировка ссылок и зависимостей
Для контроля за правильностью потоков данных и источниками ошибок в Excel используется трассировщик ячеек. При трассировке отмечаются влияющие и зависимые ячейки.
Влияющие – это ячейки, значения которых используются формулой в выделенной ячейке. Ячейка, для которой определены влияющие ячейки, всегда содержит формулу.
Зависимые – это ячейки, которые используют значение выделенной ячейки. Ячейка, для которой определены зависимые ячейки, может содержать формулу или константу.
Для трассировки необходимо выбрать пункт Сервис/Зависимости и затем либо Зависимые ячейки, либо Влияющие ячейки. Так на рис.2а и рис.2б произведена трассировка влияющих ячеек A1 и B1.
Неоднократный последовательный выбор пунктов Зависимые ячейки либо Влияющие ячейки позволяет проследить косвенно зависимые и косвенно влияющие ячейки.
Ошибочные значения
Ошибочной значение – это результат формулы, которую Excel не может вычислить. В Excel определены семь ошибочных значений (табл.6).
Таблица 6. Ошибочные значения Excel
| Ошибочное значение | Описание |
| #ДЕЛ/0! | Попытки деления на ноль. Ошибка обычно связана со ссылкой делителя на пустую ячейку |
Таблица 6. (Продолжение)
| Ошибочное значение | Описание |
| #ИМЯ? | В формуле используется имя, отсутствующее в списке имён окна диалога Присвоение имени. Excel также выводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки |
| #ЗНАЧ! | Введена математическая формула, которая ссылается на текстовое значение |
| #ССЫЛКА! | Отсутствует диапазон ячеек, на который ссылается формула |
| #Н/Д | Нет данных для вычислений. При построении модели можно ввести #Н/Д в ячейки и тем самым показать, что они зарезервированы для ожидаемых в дальнейшем данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д |
| #ЧИСЛО! | Задан неправильный аргумент функции. #ЧИСЛО! Может указывать также на то, что результат формулы слишком велик или слишком мал и не может быть представлен в ячейке |
| #ПУСТО! | В формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек |
Очистка, вставка и удаление на рабочем листе
Очистка содержимого ячеек
Характер очистки в Excel может быть разным (табл.7).
Таблица 7. Характер очистки содержимого ячеек
| Характер очистки | Описание |
| Все | Очищаются содержимое ячейки (значения и формулы), формат, например цвет и ориентация шрифта, обрамление и т.д., кроме ширины столбцов и высоты строк, примечания |
| Форматы | Удаляются форматы в выделенных ячейках, но остаются их содержимое и примечания; восстанавливается формат Общий |
| Содержимое | Удаляется содержимое выделенных ячеек, но сохраняются их форматы и примечания |
| Примечания | Удаляются примечания к выделенным ячейкам, но не затрагиваются их содержимое и форматы |
В таблице 8 описаны три способа очистки ячеек.
Таблица 8. Очистка ячеек
| Способ | Характер очистки | Описание |
| Клавиша Delete | Содержимое | Выделить ячейку или диапазон ячеек, нажать клавишу Delete |
| Команда Правка/Очистить | Все. Форматы. Содержимое. Примечание. | Выделить ячейку или диапазон ячеек, выбрать команду Правка/Очистить, в открывшемся подменю выбрать команду, соответствующую характеру очистки |
| Команда Очистить содержимое контекстного меню | Содержимое | Выделить ячейку или диапазон ячеек |
Удаление ячеек, строк и столбцов
Excel может удалять строки и столбцы целиком, а также ячейки, оставляя окружающие строки и столбцы нетронутыми. Чтобы удалить ячейки, строки и столбцы, нужно выполнить следующую последовательность действий:
1) выделить ячейку или диапазон, которые должны быть удалены, или выделить по одной ячейке в смежных строках или смежных столбцах, которые нужно удалить;
2) воспользоваться одним из нижеприведенных способов для вызова диалогового окна Удаление ячеек:
- командой меню Правка/Удалить ((Office 2003);
- на вкладке Главная командой удалить ((Office 2007);
- комбинацией клавиш Ctrl + ‘ – ‘,
- командой контекстного меню Удалить;
3) выбрать в диалоговом окне Удаление ячеек соответствующий параметр (см. табл.9) и нажать кнопку ОК.
Таблица 9. Параметры диалогового окна Удаление ячеек
| Параметр | Описание |
| Ячейки, со сдвигом влево | Ячейки, находящиеся справа от выделенных ячеек, сдвигаются влево |
| Ячейки, со сдвигом вправо | Ячейки, находящиеся снизу от выделенных ячеек, сдвигаются вверх |
| Строку | Удаляется все строки, содержащие выделенные ячейки |
| Столбец | Удаляется все столбцы, содержащие выделенные ячейки |
Для быстрого удаления строк и столбцов целиком необходимо выделить всю строку (строки) или столбец (столбцы) и воспользоваться любым из способов, приведенных в пункте 2 последовательности действий по удалению ячеек, строк, столбцов. При этом диалоговое окно Удаление ячеек открываться не будет.
Следует различать команды очистки ячеек и удаления ячеек. Первая очищает содержимое ячейки, формат или примечание, но оставляет ячейку нетронутой, а вторая полностью удаляет ячейки, строки или столбцы, а не только их содержимое.
Вставка ячеек, строк и столбцов
Excel позволяет вставлять строки и столбцы, а также добавлять ячейки, не делая изменений в целых строках и столбцах. Вставлять ячейки, строки и столбцы так же просто, как и удалять их.
Операция вставки ячейки, строки или столбца состоит из следующих шагов:
1) выделить ячейку или диапазон ячеек там, куда нужно вставить ячейку или диапазон ячеек, или выделить по одной ячейке в смежных строках или смежных столбцах, перед которыми будут вставлены строки или столбцы;
2) воспользоваться одним из нижеприведенных способов для открытия диалогового окна Добавление ячеек:
- командой меню Вставка/Ячейки (Office 2003);
- на вкладке Главная командой Вставить ячейки (Office 2007);
- комбинацией клавиш Ctrl + ‘ + ‘,
- командой контекстного меню Добавить ячейки;
3) выбрать в диалоговом окне Добавление ячеек указать нужный параметр (см. табл.10) и нажать кнопку ОК.
Таблица 10. Параметры диалогового окна Добавление ячеек
| Параметр | Описание |
| Ячейки, со сдвигом вправо | Выделенные ячейки сдвигаются вправо |
| Ячейки, со сдвигом вниз | Выделенные ячейки сдвигаются вверх |
| Строку | Вставляются строки на место каждой выделенной ячейки, а строки с выделенными ячейками сдвигаются вниз |
| Столбец | Вставляются столбцы на место каждой выделенной ячейки, а столбцы с выделенными ячейками сдвигаются вправо |
Кроме того, можно добавить строки и столбцы с помощью команды меню Вставка/Строки и Вставка/Столбцы соответственно. Перед вызовом указанных команд следует выделить ячейки, куда нужно вставить новые строки или столбцы.
Как и в случае удаления, Excel предоставляет быстрый способ вставки строк и столбцов. Он состоит в следующем. Нужно выделить строку (строки) или столбец (столбец) целиком и воспользоваться одним из способов пункта 2 последовательности шагов операции вставки.
|
|
|
