Перемещение и копирование формул
После того как формула введена в ячейку, вы можете ее перенести, скопировать или распространить на блок ячеек. При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, при этом абсолютные ссылки не изменяются, а относительные ссылки изменяются.
При копировании формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ “$”, т.е. относительные ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4.
Для перемещения формулы подведите указатель мыши к тому месту границы ячейки, где изображение указателя мыши изменяется с белого крестика на белую стрелку. Затем нажмите левую кнопку мыши и, удерживая ее, перемещайте ячейку в нужное место таблицы. Завершив перемещение, отпустите кнопку мыши. Если в записи формулы есть адреса ячеек, они при перемещении формулы не изменяются.
Для копирования формулы подведите указатель мыши к тому месту границы ячейки или блока, где изображение указателя изменяется с белого крестика на белую стрелку. Затем нажмите клавишу Ctrl и левую кнопку мыши и перемещайте ячейку в нужное место таблицы. Для завершения копирования отпустите кнопку мыши и клавишу Ctrl. Если в записи формулы есть относительные адреса ячеек, при копировании формулы они изменятся.
Выделить формулу – Копировать – Вставить – Специальная вставка
Распространение формул
Помимо копирования и перемещения формулу можно распространить на часть строки или столбца. При этом происходит изменение относительных ссылок. Для распространения формулы необходимо выполнить следующие действия:
1. Установите курсор в ячейку с формулой.
2. Подведите указатель мыши к маркеру заполнения. Изображение указателя изменяется на черный крестик.
3. Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места. Для завершения распространения формулы отпустите кнопку.
Общения об ошибках
При проведении расчетов в электронной таблице иногда в ячейках вместо ожидаемых значений выводятся различные “непонятные” результаты.
Ячейки заполнены знаками # # # # # # # # # #
Ячейка может заполняться набором символов “решетки” (#) по одной из следующих причин:
ширина столбца недостаточна для размещения численного значения, для которого установлен формат Числовой, Денежный или Финансовый. Такая ситуация встречается, например, в ячейке, в которую скопирована формула из другой ячейки, для которой был установлен один из перечисленных форматов. Для исправления ошибки необходимо либо расширить ячейку, либо изменить формат представления данных в ней;
ячейка содержит формулу, возвращающую некорректную дату или время. Excel, например, не поддерживает даты до 1900 года или отрицательные значения времени. Увеличение ширины ячейки данную ошибку не исправляет.
Ошибка #ДЕЛ/0! – возникает, при делении на ноль
Для исключения вывода ошибки можно воспользоваться функцией ЕСЛИ, которая проверяет наличие “неправильного” значения в ячейке B2:
=ЕСЛИ(B2=0; ““; –B3/B2)
Данная формула выводит “пустое” значение (““), если ячейка пустая или содержит 0; в противном случае выводится вычисленное значение корня уравнения.
Другое решение заключается в использовании функции ЕСЛИ для проверки существования любой ошибки. Следующая формула также отображает “пустое” значение в случае получения ошибки любого типа:
=ЕСЛИ(ЕОШИБКА(–B3/B2); ““; –B3/B2)
С особенностями функции ЕОШИБКА ознакомьтесь самостоятельно.
Ошибка Имя?
возникает в следующих случаях:
формула содержит неопределенное имя ячейки или диапазона. Здесь же следует отметить одну особенность Excel. Если удалить имя какой-то ячейки или какого-то диапазона, используемое в формуле, то в самой формуле оно останется (казалось бы, Excel мог бы преобразовать имена в ссылки на соответствующие ячейки, однако этого почему-то не происходит);
формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?.
Ошибка #Знач!
Ошибка весьма распространена и встречается, как правило, в следующих ситуациях.
Формула пытается провести операцию, используя некорректные данные (например, пытается сложить число и текстовое значение).
Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7.
В качестве аргументов функции использованы данные несоответствующего типа (например, в функции ЕСЛИ вместо условия использовано число или текст).
В качестве аргумента функции используется диапазон, тогда как аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6).
Ошибка #Число!возникает в одном из двух случаев:
1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);
2) формула содержит слишком большое или слишком маленькое значение. Excel поддерживает величины в пределах от 1Е-307 до 1Е307 (от 10-307 до 10307).
Ошибка #Ссылка!
Ошибка #Ссылка! возникает в случае использования формулой ошибочной ссылки на ячейку. Данная ошибка может встречаться в следующих ситуациях.
1. Ячейка, на которую ссылалась формула, была удалена. Например, следующая формула отображает ошибку #Число!, если удалена строка 20, столбец A или столбец B:
=A20/B20
Формула скопирована в новое место, где относительные ссылки на ячейки становятся недействительными. Например, если формулу = A1–1 в ячейке A2 скопировать в ячейку A1, формула вернет ошибку #Число!, так как в ней будет присутствовать ссылка на несуществующую ячейку.
Содержимое ячейки с формулой было вырезано (командой Правка — Вырезать) и затем вставлено в ячейку, на которую ссылается формула.
В заключение заметим, что в случаях, когда результатом формулы является ошибка любого из рассмотренных типов, а ширина столбца недостаточна для размещения соответствующего сообщения, ячейка заполняется знаками “#”.
Поиск ошибок в формулах
Вкладка «Формулы» - команда «Зависимости формул»
Если необходимо увидеть все ячейки, влияющие на формулу, выбираем команду «Влияющие ячейки», появятся стрелки
Если необходимо посмотреть, в каких формулах участвует ячейка, выделяем ячейку и выбираем команду «Зависимые ячейки»
Команда «Показать формулы» отображает формулы, а не числовые значения.
Команда «Проверка наличия ошибок» выявляет ошибки или определяет источник ошибки
Команда «Вычислить формулу» позволяет проверять вычисление по шагам.
Мастер функций
Функции Excel — это специальные, заранее созданные формулы для сложных вычислений, в которые пользователь должен ввести только аргументы.
Функции состоят из имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ.
Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9 — ее аргументы.
Эта формула суммирует числа в ячейках A2, и A9. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, например функция ПИ(). При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов.
Можно вводить функции на рабочем листе прямо с клавиатуры или с помощью «Мастера функций». Мастер функций – специальный инструмент приложения Excel, который имеет несколько шагов и позволяет работать с функциями.
Все функции распределены по категориям: финансовые, статистические, математические, логические, даты и времени, текстовые, инженерные.
Вызвать Мастер функций можно командой «Вставить функцию» вкладка «Формулы» или с помощью кнопки Вставка функции на строке ввода формул, будет открыто окно диалога Мастер функций – шаг 1 из 2.
В этом окне сначала следует выбирать категорию в списке Категория и затем в алфавитном списке Функция укажите нужную функцию. Для каждой функции дано краткое описание, что делает данная функция.
Excel введет знак равенства (если вы вставляете функцию в начале формулы), имя функции и круглые скобки. Затем будет открыто второе окно диалога мастера функций, в котором необходимо установить аргументы функции (в нашем случае ссылки на A2 и A9).
Второе окно диалога Мастера функций содержит по одному полю для каждого аргумента выбранной функции. Справа от каждого поля аргумента отображается его текущее значение (21 и 33). Текущее значение функции отображается внизу окна диалога (54). Нажмите кнопку ОК или клавишу Enter, и созданная функция появится в строке формул.
Аргумент может содержать значение одной ячейки, диапазон или интервал ячеек. Для указания значения одной ячейки следует щелкнуть по этой ячейки, для указания диапазона выделить диапазон, для указания интервала – указать ячейки при нажатой клавише «Enter».
В правой части окна аргумента есть значок «Свернуть», при нажатии на который окно мастера функций будет свернуто, и можно выделять аргументы, затем следует снова нажать на значок.
Математические функции
– возвращают значение математических функций
Тригонометрические функции – sin, cos, tan, asin, acos
|
Функции округления
ОКРВНИЗ Округляет число до ближайшего меньшего по модулю значения.
ОКРУГЛТ Возвращает число, округленное с требуемой точностью.
ЦЕЛОЕ Округляет число до ближайшего меньшего целого.
| ОТБР | Отбрасывает дробную часть числа. | |
| ОКРУГЛ | Округляет число до указанного количества десятичных разрядов. | |
| ОКРУГЛВНИЗ | Округляет число до ближайшего меньшего по модулю значения. | |
| ОКРУГЛВВЕРХ | Округляет число до ближайшего большего по модулю значения. |
| РАДИАНЫ Преобразует градусы в радианы. | |||
| |||
Пример. Расчет функции с округлением результата
СУММСуммирует все. числа в интервале ячеек
ПРОИЗВЕД возвращает произведениечисел в интервале ячеек
Статистические функции
| СРЗНАЧ | Возвращает среднее арифметическое аргументов. |
| СРЗНАЧЕСЛИ | Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые удовлетворяют данному условию. |
| СРЗНАЧЕСЛИМН | Возвращает среднее значение (среднее арифметическое) всех ячеек, которые удовлетворяют нескольким условиям. |
| СЧЁТ | Подсчитывает количество чисел в списке аргументов. |
| СЧЁТЗ | Подсчитывает количество значений в списке аргументов. |
| СЧИТАТЬПУСТОТЫ | Подсчитывает количество пустых ячеек в диапазоне |
| СЧЁТЕСЛИ | Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию |
| СЧЁТЕСЛИМН | Подсчитывает количество ячеек внутри диапазона, удовлетворяющих нескольким условиям. |
| МАКС | Возвращает наибольшее значение в списке аргументов |
| МИН | Возвращает наименьшее значение в списке аргументов. |
Привести примеры: определение – мин, макс, функций – счет, счетесли
Логические функции
| И | Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА. |
| ЛОЖЬ | Возвращает логическое значение ЛОЖЬ. |
| ЕСЛИ | Выполняет проверку условия, позволяет использовать условие при вычислениях |
| ЕСЛИОШИБКА | Возвращает введённое значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат вычисления. |
| НЕ | Меняет логическое значение своего аргумента на противоположное. |
| ИЛИ | Возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА. |
| ИСТИНА | Возвращает логическое значение ИСТИНА. |
Привести пример функции ЕСЛИ, расчет функции с условием.
Функции дата и время
В Microsoft Office Excel даты хранятся в виде последовательности (порядковых номеров) чисел. Например, день 1 января 1900 г. имеет номер 1, а 1 января 2008 г. — 39 448, т. к. интервал между этими датами составляет 39 448 дней. Временные значения хранится в виде дробной части этого числа, поскольку они рассматриваются как доли суток. Поскольку даты и временные значения представлены в виде чисел, их можно складывать и вычитать, а также использовать в других вычислениях. При использовании формата «Общий» для ячеек, содержащих значения даты и времени, можно отобразить дату в виде числа или время в виде дробной части этого числа.
| ТДАТА | Возвращает текущую дату и время | |
| СЕГОДНЯ | Возвращает текущую дату. =СЕГОДНЯ() | |
| ДАТА | Возвращает заданную дату в числовом формате. =ДАТА() следует указать – год, месяц, день | |
| ДАТАЗНАЧ | Преобразует дату из текстового формата в числовой формат. =ДАТАЗНАЧ("22-08-2018") – текстовый формат – в кавычках, (если получено число следует изменить формат) | |
| ГОД | Преобразует дату в числовом формате в год. =ГОД(A1) – (А1 – дата в формате день – месяц – год) | |
| МЕСЯЦ | Преобразует дату в числовом формате в месяцы. =МЕСЯЦ(A1) – (А1 – дата в формате день – месяц – год) | |
| ДЕНЬ | Преобразует дату в числовом формате в день месяца. =ДЕНЬ(A1) – (А1 – дата в формате день – месяц – год) | |
| ДНЕЙ360 | Вычисляет количество дней между двумя датами на основе 360-дневного года. =ДНЕЙ360(A1;A2) | |
| ЧИСТРАБДНИ | Возвращает количество рабочих дней между двумя датами. Рабочими днями не считаются выходные дни и дни, определенные как праздничные (диапазон) = ЧИСТРАБДНИ(A2;A1; А22:А39) | |
| РАБДЕНЬ | Возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты. Функция РАБДЕНЬ используется для исключения выходных дней или праздников при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней. | |
| ВРЕМЯ | Возвращает заданное время в числовом формате. | |
| ЧАС | Преобразует дату в числовом формате в часы. | |
| МИНУТЫ | Преобразует дату в числовом формате в минуты. | |
| СЕКУНДЫ | Преобразует дату в числовом формате в секунды. | |
| ВРЕМЗНАЧ | Преобразует время из текстового формата в числовой формат. | |
Пример. Ячейка К12 – содержит дату (год – месяц –день), прибавить 2 месяца
=ДАТА(ГОД(К12); МЕСЯЦ(К12) + 2; ДЕНЬ(К12))
вызвать функцию ДАТА, затем в строке формул «Мастер функций», поставить ;, возврат в окно функции ДАТА и т.д.
Пример. Ячейка К12 – содержит дату (год – месяц –день) поступления на работу. Определить стаж работы. Стаж определяется как полное число лет от даты поступления на работу до текущей даты (год – 360 дней).
=ОКРУГЛВНИЗ(ДНЕЙ360(К12; $k$20)/360;0) $k$20 – текущая дата
Пример. В ячейку А3 введена дата, проверить дата относится к 2011 году, если нет выдать сообщение.
=ЕСЛИ(ГОД(C85)=2011;"относится";"нет")
определить количество полных дней между двумя датами
=ДНЕЙ360(B89;E99;1)
определить количество рабочих дней от начальной даты до конечной, например для больничного
=ЧИСТРАБДНИ(B89;F96;H88:H101)
определить дату доставки с учетом праздников, доставка через 10 дней
=РАБДЕНЬ(D92;10;F92:F105)
определить сколько минут прошло от начального значения времени до конечного
Работа с массивами
Часто при работе с таблицами возникает необходимость применить одну и туже операцию к целому диапазону ячеек или произвести расчеты по формулам, зависящим от большого массива данных. Под массивом понимается прямоугольный диапазон формул или значений, которые приложение обрабатывает как единое целое.
Результатом формулы массива может быть массив значений или число.
Что ввести формулу для работы с массивом следует:
1. выделить диапазон ячеек, где будет получен результат
2. ввести формулу для расчета
3. завершить ввод формулы нажатием комбинации клавиш Ctrl + Shift + Enter – формула будет заключена в фигурные скобки
Пример. Поэлементное умножение элементов массива
введем числовые значения в диапазон A1:B3 и диапазон D1:E3
введем формулу {=A1:B3*D1:E3} результат A1*D1; A2*D2; B1*E1
Текстовые функции
| ТЕКСТ | Форматирует число и преобразует его в текст. аргумент – либо числовое значение, либо формула, которая вычисляет числовое значение, либо ссылка на ячейку, которая содержит числовое значение, второй аргумент – числовой формат с вкладки число =ТЕКСТ(C8;"0,00") =ТЕКСТ(B20;"0,00р.") |
| СОВПАД | Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ — в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Аргумент – строка, формулу можно растянуть. =СОВПАД(A13;M18) =СОВПАД(Лист1!A13;A1) =СОВПАД(A1;"ручка") Функция СОВПАД позволяет проверить, входит ли некий заданный текст в документ. Проверка на полное совпадение =ИЛИ(СОВПАД($K$1;A1:A7)) K1 – искомое значение, А1:А7 - диапазон |
| СЦЕПИТЬ | Объединяет две или более текстовых строк в одну. =СЦЕПИТЬ($A$13;" ";A14) или =$A$13&" "&A14 |
| НАЙТИ | Функции НАЙТИ находят вхождение одной текстовой строки в другой строке и возвращает начальную позицию первой строки относительно крайнего левого знака второй строки. =НАЙТИ("р";A13;1) 1- номер позиции, с которой начинается поиск. |
| ДЛСТР | возвращает количество символов в текстовой строке =ДЛСТР(A13) |
| ПРОПИСН | преобразует все строчные буквы в прописные =ПРОПИСН(A13) |
| ПРОПНАЧ | преобразует первую букву в прописную, остальные строчные =ПРОПНАЧ(A14) |
Функции поиска информации
| АДРЕС | Возвращает ссылку на отдельную ячейку листа в виде текста. АДРЕС(номер_строки;номер_столбца ;тип_ссылки;а1;имя_листа) аргументы Номер_строки — номер строки, используемый в ссылке ячейки. Номер_столбца — номер столбца, используемый в ссылке ячейки. Тип_ссылки — задает тип возвращаемой ссылки.
| ||||||||
| ИНДЕКС | Возвращает значение элемента массива , заданного номером строки и номером столбца. Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов. =ИНДЕКС( B4:B13; 4; 4) | ||||||||
| ПОИСКПОЗ | Просматривает массив до тех пор, пока в нем не встретится искомое значение. Возвращает позицию первого значения искомого значения. Аргументы: искомое значение, массив, тип сопоставления: 0 – ищет на полное соответствие 1 – ищет в упорядоченном массиве по возрастанию наибольшее значение, которое меньше или равно значению аргумента -1 - ищет в упорядоченном массиве по убыванию наименьшее значение, которое меньше или равно значению аргумента =ПОИСКПОЗ(H2;A2:A11;0) Пример. Найти в списке товаров - заданный | ||||||||
| СТОЛБЕЦ | Возвращает номер столбца, на который указывает ссылка. =СТОЛБЕЦ(H2) – результат 8 | ||||||||
| СТРОКА | Возвращает номер строки, определяемой ссылкой. | ||||||||
| ЧИСЛСТОЛБ | Возвращает количество столбцов в ссылке. =ЧИСЛСТОЛБ(A1:C11) | ||||||||
| ВПР | Ищет значение в первом столбце массива и возвращает значение из указанного столбца этой строки массива. Пример. ВПР("Ластик";A2:C11;2) – результат значение из второго столбца массива | ||||||||
| ГПР | Ищет значение в первой строке массива и возвращает значение из указанной строки этого столбца массива. | ||||||||
| ТРАНСП | Возвращает транспонированный массив. Меняет ориентацию массива с вертикальной на горизонтальную и наоборот. Формула вводится как формула массива. |
Функции проверки свойств
Используются для проверки типа значения или ссылки. Каждая функция проверяет тип значения и возвращает в зависимости от него значение ИСТИНА или ЛОЖЬ
| ЕПУСТО | Возвращает «Истина», если нет пустых ячеек в выделенном диапазоне и «Ложь», если есть. Пример. Определить количество пустых ячеек. |
| ЕЧИСЛО | Проверяет, является ли значение ячейки или всех ячеек диапазона - числом |
| ЕССЫЛКА | Проверяет, содержит ячейка или все ячееки диапазона - адрес |
| ЕТЕКСТ | Проверяет, является ли значение ячейки или всех ячеек диапазона - текстом |
Построение диаграмм
Диаграмма – графическое изображение зависимости между величинами. С помощью приложения Excel можно создавать сложные диаграммы для данных рабочего листа Диаграммы являются наглядным средством представления данных рабочего листа. Диаграмму можно создать на отдельном листе или поместить в качестве внедренного объекта на лист с данными.
Типы диаграмм
· гистограмма – отображение данных в виде прямоугольников (цилиндров, конусов) – ось У – значение отображаемой величины (высота), используется для сравнения данных по значению;
· график – отображение в виде кривой (гладкой или в виде отрезков), ось Х размечается 1, 2, 3 и т.д.;
· круговая (кольцевая) – показывает зависимость в виде долей (процентов), все значения, которые выделены для построения диаграмм, суммируются, затем определяется доля каждого значения;
· линейчатая – данные отображаются в виде прямоугольников, которые расположены горизонтально, используется для сравнения данных по значению;
· точечная – данные отображаются, в виде графика, ось Х размечается по первому выделенному столбцу, точки могут быть не соединены, для построения графика математической функции используют – точечная – гладкая кривая
· лепестковая – каждая категория имеет свою ось, линии соединяющие ось образуют ряд данных