Создание и использование формул в Excel Автор: Зелёная Е.В.

Содержание 1. Операции и приоритеты 2. Ввод формул 3. Ссылки. Виды ссылок 4. Использование текста в формулах 5. Использование имен 6. Режимы вычислений по формулам 7. Присваивание имен константам и формулам

Операции и приоритеты Операторы (обозначения математических операций, совершаемых над операндами ) Арифметические операторы Операторы сравнения Текстовый оператор конкатенации Оператор ссылки

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

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

Ввод формул пусть требуется ввести в ячейку А1 сумму чисел 10 и 20. Для этого надо выделить ячейку А1 и ввести =10+20 пусть требуется ввести в ячейку А1 сумму чисел 10 и 20. Для этого надо выделить ячейку А1 и ввести =10+20 После нажатия Enter в текущей ячейке А1 появилось вычисленное значение – число 30, а в строке формул – сама формула, по которой произведено вычисление После нажатия Enter в текущей ячейке А1 появилось вычисленное значение – число 30, а в строке формул – сама формула, по которой произведено вычисление

Ссылки. Типы ссылок Ссылки в Excel бывают 3-х типов: Относительные ссылки (пример: A1); Абсолютные ссылки (пример: $A$1); Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Относительные ссылки При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

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

Именованные ссылки Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl"). Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl").

Стиль трёхмерных ссылок Ссы лка Значение R[- 2]C Относительная ссылка (Относительная ссылка. В формуле адрес ячейки, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме A1.) на ячейку, расположенную на две строки выше и в том же столбце R[2] C[2] Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее R2C 2 Абсолютная ссылка (Абсолютный адрес ячейки. Часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат $A$1.) на ячейку, расположенную во второй строке и во втором столбце R[- 1] Относительная ссылка на строку, расположенную выше текущей ячейки RАбсолютная ссылка на текущую строку

Использование текста в формулах текст должен быть заключен в двойные кавычки. текст должен быть заключен в двойные кавычки. Неправильно: =$55+$33 Неправильно: =$55+$33 Правильно: ="$55"+$"33" Правильно: ="$55"+$"33" При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88. При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88. Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Юрий", а ячейка А2 - "Кордык", то введя в ячейку А3 следующую формулу =А1&А2, получим "Юрий Кордык". Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2. Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2, мы получим "10 мешков". Причем результатом такого объединения будет текстовое значение. Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Юрий", а ячейка А2 - "Кордык", то введя в ячейку А3 следующую формулу =А1&А2, получим "Юрий Кордык". Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2. Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2, мы получим "10 мешков". Причем результатом такого объединения будет текстовое значение.

Использование формул Для ввода формулы с использованием имен ячеек выполните следующие действия Для ввода формулы с использованием имен ячеек выполните следующие действия 1. Присвойте имена своим ячейкам. 2. Расположите табличный курсор в ячейке, в которую будет введена формула. 2. Расположите табличный курсор в ячейке, в которую будет введена формула. 3. Введите с клавиатуры знак равенства (=), обозначающий начало формулы. 3. Введите с клавиатуры знак равенства (=), обозначающий начало формулы. 4. Выберите первую ячейку, входящую в формулу, выделив ее имя в окне списка в строке формул 4. Выберите первую ячейку, входящую в формулу, выделив ее имя в окне списка в строке формул 5. Введите с клавиатуры арифметический оператор, используемый в данной формуле. 5. Введите с клавиатуры арифметический оператор, используемый в данной формуле. 6 Укажите вторую ссылку на ячейку, которая присутствует в формуле, выбрав ее имя из окна списка имен в строке формул. 6 Укажите вторую ссылку на ячейку, которая присутствует в формуле, выбрав ее имя из окна списка имен в строке формул. 7. Щелкните на кнопке Ввод (Enter) или нажмите клавишу для завершения ввода формулы. 7. Щелкните на кнопке Ввод (Enter) или нажмите клавишу для завершения ввода формулы.

Режимы вычисления по формулам

Именование константы чтобы создать константу, выполните следующие действия. чтобы создать константу, выполните следующие действия. Выберите команду Вставка/Имя/Присвоитъ (Insert/Name/Create), чтобы открыть диалоговое окно Присвоение имени (Define Name). Выберите команду Вставка/Имя/Присвоитъ (Insert/Name/Create), чтобы открыть диалоговое окно Присвоение имени (Define Name). В диалоговом окне Присвоение имени в поле Имя (Name) введите имя кон­ станты (в нашем примере Налог). В диалоговом окне Присвоение имени в поле Имя (Name) введите имя кон­ станты (в нашем примере Налог). Щелкните в поле Формула (Refers to) и замените текущий адрес на выбранное значение. Щелкните в поле Формула (Refers to) и замените текущий адрес на выбранное значение. Щелкните на кнопке Добавить (Add). Щелкните на кнопке Добавить (Add). Щелкните на кнопке ОК, чтобы закрыть диалоговое окно. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно. Присвоив константе имя, можно использовать его в формулах электронной таблицы следующим образом. Присвоив константе имя, можно использовать его в формулах электронной таблицы следующим образом. 1. Наберите имя, присвоенное константе, в выбранной области формулы. 1. Наберите имя, присвоенное константе, в выбранной области формулы. 2. Вставьте имя, назначенное константе, выполнив команду Вставка/Имя/Вставить (insert/Name/Paste), а затем указав требуемое имя в диалоговом окне Вставить имя (Paste Name). 2. Вставьте имя, назначенное константе, выполнив команду Вставка/Имя/Вставить (insert/Name/Paste), а затем указав требуемое имя в диалоговом окне Вставить имя (Paste Name).