Формулы

Вычисления в таблицах

Ознакомление с правилами ввода простых формул

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

Части формулы

Функции. Функция ПИ() возвращает значение числа Пи: 3,142...

Ссылки. A2 возвращает значение ячейки A2.

Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

Операторы. Оператор ^ ("крышка") возводит число в степень, а оператор * ("звездочка") перемножает два или более числа.

Исправление распространенных ошибок во время ввода формул

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

Убедитесь, что…	Дополнительные сведения
Каждая формула начинается со знака равенства (=)	Если знак равенства опустить, введенные значения могут быть отображены как текст или дата. Например, если ввести СУММ(A1:A10), MicrosoftExcel отобразит текстовую строку СУММ(A1:A10) и не станет вычислять значение формулы. Если ввести 11/2, Excel отобразит дату (например, "2 ноября" или "02.11.2009") вместо того, чтобы разделить 11 на 2.
Все открывающие и закрывающие скобки согласованы	Убедитесь, что у каждой скобки имеется соответствующая ей пара. Чтобы функция в формуле работала правильно, важно, чтобы каждая скобка стояла на своем месте. Например, формула =ЕСЛИ(B5<0);"Неверно";B5*1,05) работать не будет, потому что в ней две закрывающие и только одна открывающая скобка. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0;"Неверно";B5*1,05).
Для указания диапазона используется двоеточие	При указании ссылки на диапазон ячеек используйте двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например: A1:A5.
Введены все необходимые аргументы	Некоторым функциям листа требуются аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.), тогда как другие функции (например, ПИ) аргументов не принимают. Кроме того, убедитесь, что вы не ввели слишком много аргументов. Например, функция ПРОПИСН принимает в качестве аргумента только одну строку текста.
Введены аргументы правильного типа	Для некоторый функций листа, например СУММ, требуются числовые аргументы. Другие функции, напримерЗАМЕНИТЬ, требуют текстового значения по меньшей мере для одного из аргументов. Прииспользования в качестве аргумента данных неверного типа можно получить неправильные результаты или ошибку.
Количество вложенных функций не превышает 64	Внутри одной функции можно ввести (или вложить) не более 64 уровней функций. Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2;"Меньше двух!";"Больше двух!") содержит три функции: функция ПИ вложена в функцию КОРЕНЬ, которая, в свою очередь, вложена в функцию ЕСЛИ.
Имена других листов заключены в одинарные кавычки	Если в формуле имеются ссылки на значения или ячейки других листов или книг, имена которых содержат небуквенные знаки, такие имена необходимо заключить в одинарные кавычки (').
Ссылаясь на лист в формуле, следует поместить после его имени восклицательный знак (!).	Например, чтобы вернуть значение ячейки D3 листа с именем "Данные за квартал" в той же книге, воспользуйтесь формулой ='Данные за квартал'!D3.
Включен путь к внешним книгам	Убедитесь, что каждая внешняя ссылка (Внешняя ссылка. Ссылка на ячейку или диапазон ячеек в другой книге Microsoft Excel или ссылка на имя, определенное в другой книге.) содержит имя книги и путь к ней. Ссылка на книгу включает в себя имя этой книги и должна быть заключена в квадратные скобки ([]). Кроме того, в ссылке должно быть указано имя листа в книге. Например, формула со ссылкой на ячейки с A1 по A8 на листе с именем "Продажи" в книге (в настоящий момент открытой в Excel) с именем Кв2 Операции.xlsx, будет выглядеть примерно так: =[Кв2 Операции.xlsx]Продажи!A1:A8. Если книга, на которую требуется сослаться, не открыта в Excel, ссылку на нее все же можно включить в формулу. Нужно указать полный путь к файлу, как в следующем примере: =ЧСТРОК('C:\Мои документы\[Кв2 Операции.xlsx]Продажи'!A1:A8). Эта формула возвращает число строк в диапазоне, включающем ячейки с A1 по A8 в другой книге (а именно, 8). Примечание Если полный путь содержит пробелы, как в предыдущем примере, его необходимо заключить в одинарные кавычки (в начале пути и после имени листа, перед восклицательным знаком).
Числа введены без форматирования	Не форматируйте числа, которые вводите в формулах. Например, если вам нужно ввести значение $1,000, введите в формулу просто 1000. Если ввести запятую как часть числа, Excel будет трактовать ее как разделительный знак. Если требуется, чтобы числа отображались с разделителями тысяч и миллионов или с символами валюты, отформатируйте ячейки после ввода чисел. Например, если нужно добавить 3100 к значению в ячейке A3, и вы ввели формулу =СУММ(3,100;A3), приложение Excel сложит числа 3 и 100, а затем добавит результат к значению ячейки A3, вместо того чтобы добавить 3100 к значению A3. А если введена формула =ABS(-2,134), будет показана ошибка, поскольку функция ABS принимает только один аргумент.
Избегайте деления на ноль	При попытке разделить значение в ячейке на значение в другой ячейке, содержащей ноль или не содержащей никакого значения, будет показана ошибка #ДЕЛ/0!.

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

Применение операторов в формулах

Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок

Арифметические операторы.

Арифметическийоператор	Значение	Пример
+ (знак плюс)	Сложение	23+3
– (знак минус)	Вычитание / Унарный минус	35–166 / –1
* (звездочка)	Умножение	23*(-321)
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ (крышка)	Возведение в степень	3^2 (аналогично 3*3)

Операторы сравнения.

Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

Операторсравнения	Значение	Пример
= (знак равенства)	Равно	A1=B1
> (знак больше)	Больше	A1>B1
< (знак меньше)	Меньше	A1<B1
>= (знак больше и знак равенства)	Больше или равно	A1>=B1
<= (знак меньше и знак равенства)	Меньше или равно	A1<=B1
<> (знак «не равно»)	Не равно	A1<>B1

Текстовый оператор конкатенации. Операция (&) используется для объединения нескольких текстовых строк в одну строку.

Текстовый оператор	Значение	Пример
& (амперсанд)	Объединение последовательностей символов в одну последовательность.	Выражение "Северный " & " ветер" эквивалентно строке "Северный ветер".

Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы.

Оператор ссылки	Значение	Пример
: (двоеточие)	Оператор диапазона используется для ссылки на все ячейки между крайними ячейками диапазона, включая эти ячейки.	B5:B15
, (запятая)	Оператор объединения объединяет несколько ссылок в одну ссылку.	СУММ(B5:B15,D5:D15)
(пробел)	Оператор пересечения задает пересечение двух диапазонов.	A6:D6

Порядок выполнения действий в формулах

Если в формуле содержится несколько операторов, то порядок вычислений в MicrosoftExcel определяется приоритетом операторов. Раньше выполняются операции с более высоким приоритетом, затем — с менее высоким. Для изменения порядка выполнения операций используются скобки. Вначале вычисляются те части формулы, которые заключены в скобки, затем — остальные части.

	Оператор	Описание
	: (двоеточие) (пробел) , (запятая)	Операторы ссылок
	–	Унарный минус (например –1)
	%	Процент
	^	Возведение в степень
	* и /	Умножение и деление
	+ и –	Сложение и вычитание
	&	Объединение двух текстовых строк в одну
	= <><= >= <>	Сравнение

Адреса, которые используются в формулах, получили название ссылок. Ссылки позволяют связывать между собой любые ячейки электронной таблицы и проводить необходимую обработку табличных данных.

Ссылка — это адрес объекта (ячейки, строки, столбца, диапазона), используемый при записи формулы.

Способы ввода формул: набрать ее

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

ВЫПОЛНЕНИЕ РАБОТЫ:

1. Изучите краткие сведения из теории.

2. Создайте папку с именем ЛРЭТ. Создать книгу Основы.

1. Ввод данных

Активизировать рабочий Лист1.

Лист переименовать в Ввод данных. Переименование листа:

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

1.1. Ввод текстовой информации

Задание 1.1. В ячейку А1 внести: «Ввод информации».

¿ В ячейку А1 записать: «Ввод информации». Во время ввода данных в строке формул появляются три кнопки: отмена последнего действия, для сохранения изменений при вводе, Мастер функций.

Для завершения операции ввода текста надо выполнить одно из трех действий:

· Щелкнуть левой кнопкой мыши по любой другой ячейке.

· Нажать клавишу Enter.

· Нажать одну из клавиш перемещения курсора.

Задание 1.2. Изменить содержимое ячейки А1, добавить слово текстовой перед словом информации.

1 способ:

· Сделать ячейку А1 активной (выделить ячейку – щелкнуть по ней), а затем нажать F2 (курсор поместиться в ячейку).

· И в самой ячейке добавить нужное слово.

2 способ:

· Дважды щелкнуть мышью по ячейке.

· И в самой ячейке добавить нужное слово.

Завершить операцию редактирования одним из трех действий, приведенных в задании 1.1.

Задание 1.3. Число и текст

1. В ячейку А2 внести год: 1380, а ячейку В2: . Чтобы данные в ячейке В2 воспринимались как текстовые, ввод информации нужно начать с апострофа или пробела, иначе информация будет интерпретирована как числовая и примет вид

2. В ячейку А3 внести время начала занятия (например, 8:30), а в ячейку В3: -- время начала занятия.

1.2. Ввод чисел и форматирование числовых данных

Задание 1.4. Разделители

1. В ячейку А4 внести: «Ввод чисел и форматирование данных».

2. В ячейку А5 внести дробное число 3,14 (между целой и дробной частью ставится запятая).

3. В ячейку А6 ввести отрицательное число -56.

4. В ячейку А7 внести число с нулевой целой частью,769 (при наборе числа нулевую целую часть можно опускать).

5. В ячейку А8 внести число 0,000 000 000 8 (все цифры числа набрать без пробелов, а затем установить для ячейки формат с разделителями, для этого нажать на вкладке Главная в группе Число кнопку Формат с разделителем ). Результат: .

До нажатия

В ячейку А9 внести число 456 456 789 789. Если появится «решетка» , то необходимо расширить ширину столбца: .

1.3. Ввод даты и изменение ее формата

Задание 1.5. Форматы

1. В ячейку D5 внести дробное число 0,45789. Изменить формат ячейки, оставив два знака после запятой (кнопкой Уменьшить разрядность на вкладке Главная в группе Число). Число должно отобразиться в следующем виде: 0,46. Проверить себя: в строке формул это число отображается в первоначальном виде.

2. В ячейку D6 внести число 45789. Изменить формат на денежный: сделать ячейку активной; вкладка Главная – группа Ячейки – Формат ( раскрыть список ) – Форматячеек…; в окне Формат ячеек перейти на вкладку Число; в списке Числовые форматы выбрать Денежный формат; в поле Число десятичных знаков установить ноль; в поле Обозначение выбрать р.; ОК ..

3. В ячейку D7 внести число 450000000000 (450 миллиардов). Обратите внимание, что после подтверждения ввода данных число записалось в виде 4,5Е+11(4,5*10¹¹ – математическая запись такого числа). В строке формул число отображается в том виде, в котором было введено.

4. Изменить формат ячейки D7 на числовой:

· сделать ячейку активной (курсор не должен находится внутри ячейки );

· вкладка Главная – группа Ячейки – Формат ( раскрыть список ) – Форматячеек…; в окне Формат ячеек перейти на вкладку Число;

· в списке Числовые форматы выбрать Числовой;

· в поле Число десятичных знаков установить ноль и установитьфлажок Разделитель групп разрядов; ОК ..

5. В ячейку D8 внести число 0,2 – процент. Изменить формат ячейки D8 на 20% (кнопка Процентный формат на вкладке Главная в группе Число).

Задание 1.6. Дата

1. В ячейку B9 внести: «Ввод даты».

2. В ячейку А10 внести дату 24.09.2007. Изменить формат на вид: 24 сентября 2007г. (сделать ячейку активной; вкладка Главная – группа Ячейки – Формат ( раскрыть список ) – Форматячеек…; в окне Формат ячеек перейти на вкладку Число; в списке Числовые форматы выбрать Дата; в списке Тип выбрать соответствующий вариант; ОК.).

1.4. Заполнение ячеек

Задание 1.7. Маркер заполнения

1. В ячейку А11 внести: «Заполнение ячеек».

2. Заполнить ячейки А12:А17, А12:Е17 значениями «имя» (имя – это имя выполняющего работу):

• В ячейку А12 внести свое имя - установить курсор мыши на маркер заполнения: черный прямоугольник в правом нижнем углу ячейки (курсор должен принять вид сплошного черного крестика).
• при нажатой левой кнопке мыши потянуть за маркер заполнения вниз на 5 строчек.
• Аналогично заполнение выполнить вправо.

Выделить ячейку (диапазон) Установить курсор на маркер заполнения При нажатой левой кнопке мыши протянуть маркер в необходимом направлении (вниз, влево и т.д.).
Рисунок 3 – Заполнение ячеек

3. Заполнить ячейки Е4:Е11 значениями «вторник, среда и т.д.»:

• В ячейку Е4 внести название дня недели вторник.

Рисунок 4 – Варианты автозаполнения

• Протащить маркер заполнения до ячейки Е11. Выполнить аналогично заполнение вверх.

4. Заполнить ячейки С18:К18 значениями «июль, август и т.д.»

• В ячейку С18 внести название месяца июль. Протащить маркер заполнения до ячейки К18. Выполнить аналогично заполнение влево.

5. Заполнить ячейки Н12:К12 значениями «КИТ1, КИТ2 и т.д.»

6. Создать ряд Сезоны года: зима, весна, лето, осень.

Задание 1.8. Заполнить ячейки L1: L10 значением 5.

Задание 1.9. Записать, в столбец числа 0,5; 0,6 и т.д.

¿ В ячейку M1 записать число 0,5; в ячейку M2 - число 0,6 - выделить ячейки M1:M 2 - протянуть маркер заполнения до нужной ячейки (например, M10).

Задание1.10. Опробовать разные варианты автозаполнения с использованием правой кнопки мыши:

¿ В ячейку N1 записать, например, число 25,8 - при нажатой правой клавише мыши протянуть маркер заполнения до ячейки N10 - далее опробовать варианты Копировать ячейки, Заполнить, заполнить форматы, Заполнить значения.

Задание1.11. Заполнить ячейки Fl:F10 значениями от 3,4 с шагом 0,6.

¿ В ячейку F1 записать число 3,4 - при нажатой правой клавише мыши протянуть маркер заполнения до ячейки F10- в контекстном меню выбрать Прогрессия - Тип: арифметическая -Шаг: 0,6 - [ОК].

Задание 1.12. Ячейки A20:F20 заполнить значениями от 100, с последующим, уменьшением в 20 раз.

По сути это будет геометрическая прогрессия с первым членом 100 и знаменателем 1/20 = 0,05.

¿ В ячейку А20 записать число 100 - при нажатой правой клавише мыши протянуть маркер заполнения до ячейки F20- Прогрессия -Геометрическая -Шаг: 0,05; [ОК].

Задание1.13. Выполнить автозаполнение ячеек значениями дат.

¿ В ячейку H20 поместить дату (например, 20.10.06) - при нажатой правой клавише мыши протянуть маркер заполнения до ячейки Н30 - далее опробовать разные варианты заполнения.

Задание1.14. Выполнить заполнение ячеек A22:F22 значениями Луна 1, Луна 2 и т.д.

¿ В ячейку А22ввести текст Луна 1 - при нажатой левой кнопке мыши выполнить заполнение ячеек.

2. Вычисления

Активизировать рабочий Лист2.

Лист переименовать в Вычисление.

Задание 2.1. Вычислить 5^3,45 -7,3x9. Задание выполняем на листе Вычисление.

Вариант 1. В ячейку Al ввести формулу =5^{^}3,45-7,3*9 (знак «^» находится на клавише «»).

После нажатия клавиши [ENTER] в ячейке появится результат вычисления.

Вариант 2. Все числа, входящие в выражения, ввести в отдельные ячейки:

¿ В ячейки ВЗ:В6 ввести числа 5; 3,45; 7,3; 9 - в ячейку В7 ввести формулу =ВЗ^{^}В4-В5*В6 (ввод склавиатуры)-[ENTER].

¿ Поместить обозначения переменных в таблицу: в ячейки АЗ:А6 ввести названия переменных величин а, b, с, d; в ячейку А7 - название результата: у.Это соответствует вычислению по формуле y=a^b-cd

С помощью	клавиатуры	мыши ввести формулы
Рисунок 5 - Вычисления

¿ Меняя содержимое ячеек ВЗ:В6, производить наблюдение за изменением результата расчета по формуле в ячейке В7.

Вариант 3. Ввести формулу в ячейку С7. При вводе формулы ссылки на ячейки формировать с помощью мыши, а не клавиатуры.

¿ В ячейку С7 ввести знак «=»-щелкнуть в ячейке ВЗ (в строке формул появится ссылка на ячейку ВЗ) - ввести знак «^{^}» - щелкнуть в ячейке В4 - знак «-» - щелкнуть в ячейке В5 - знак «*» - щелкнуть в ячейке В6 - [ENTER].

В дальнейшей работе такой прием формирования ссылок на ячейки должен стать преобладающим.

Задание2.2. Изменить формулу в ячейке С7 на у=с^b-ad.

Выполнить задание безудаления старой формулы и ввода новой. Нужно воспользоваться редактированием старой.

¿ Выделить ячейку с редактируемой формулой (С7).

Формула отобразится в строке формул (в верхней части таблицы над строкой с буквенным обозначением столбцов). Дальнейшее редактирование произвести в строке формул:

¿ ссылку на ячейку ВЗ заменить ссылкой на ячейку В5 - ссылку на ячейку В5 заменить ссылкой на ячейку ВЗ - [ENTER].Окончательно формула будет иметь вид .

Задание2.3. Сформировать формулы для вычисления логических выражений:w<6;w>v; v ≤ t⁴; (t-w) ≠ (t+v).

¿ Поместить обозначения переменных в таблицу: в ячейки А10:С10ввести названия переменных величин w, v, t. В ячейки A11:C11 ввестилюбые их значения.

¿ Формулы ввести в ячейки В12:В15, а в ячейки А12:А15- текст для обозначения содержимого ячеек: «w<6», «w>v» и т.д..

¿ В ячейку В12 поместить формулу =А11<6.

В зависимости от содержимого ячейки А11 (w) результат будет ИСТИНА или ЛОЖЬ.

Рисунок 6 – Логические выражения (формулы)	Рисунок 7 – Логические выражения (результат)

¿ Ввести формулы в ячейки В11:В13.

¿ Изменяя содержимое ячеек ВЗ:В6, наблюдать за изменением результата вычисления логических выражений.

Задание2.4. Вычислить

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

¿ В ячейку ЕЗ ввести текст «1 слагаемое»; скопировать ячейку ЕЗ в ячейки Е4:Е5 и откорректировать записи; в ячейку Е6 поместить текст «у=».

¿ В ячейку F3 поместить формулу =(1+В4)^{^}2,7. В ячейку F4 ввести формулу =(ВЗ+В4)^{^}2, а в ячейку F5 - формулу =F3/(4+F3). В ячейку F6 ввести формулу =F3+F4-F5.

Дата добавления: 2014-12-16