- •1. Основные термины и обозначения
- •1.1. Интерфейс программы
- •1.2. Рабочая книга
- •Контрольные вопросы
- •2. Ввод и форматирование данных
- •2.1. Общие положения
- •2.2. Ввод и форматирование текста
- •2.3. Ввод и форматирование чисел
- •2.4. Процедура автозаполнения
- •2.5. Формулы
- •2.6. Встроенные функции
- •2.7. Условное форматирование
- •2.8. Контрольные вопросы
- •3. Графическое отображение данных
- •3.1. Работа с диаграммами
- •3.2. Выбор формы зависимости временного ряда
- •3.3. Контрольные вопросы
- •4. Использование списков (базы данных)
- •4.1. Создание и фильтрация списка
- •4.2. Подведение итогов
- •4.3. Сводные таблицы
- •4.4. Контрольные вопросы
- •5. Анализ данных
- •5.1. Процедура Подбор параметра
- •5.2. Сценарии
- •5.3. Таблицы подстановки данных
- •5.4. Процедура Поиск решения
- •5.5. Контрольные вопросы
- •6. Основы программирования на vba
- •6.1. Общие положения
- •6.2. Макросы и редактор vba
- •6.3. Некоторые приемы программирования
- •6.4. Использование элементов управления форм в листах Excel
- •6.5. Контрольные вопросы
- •Библиографический список
- •Оглавление
2.5. Формулы
Формулой в Excel называется последовательность символов, начинающаяся со знака равенства. В эту последовательность могут входить:
константы;
адреса и имена ячеек (диапазона ячеек);
встроенные функции;
операторы +, -, *, /, ^, %, =, <>, <, >, <=, >=.
Результат вычислений (числовой или текстовый) отображается в ячейке, а сама формула – в строке формул. Программа нигде не хранит вычисленные по формуле значения – только саму формулу. Если результат вычисления формулы окажется длиннее ширины столбца, в ячейке появляются символы вида #. Для отображения результата следует увеличить ширину столбца.
При копировании формулы в другие ячейки относительный адрес настраивается на новый столбец и новую строку, а абсолютный адрес остается неизменным. Например, если адрес в формуле содержит ссылку $D$18, то при копировании формулы в адресе не меняется ни имя столбца, ни номер строки. Если адрес в формуле имеет вид D$18 (смешанная ссылка), то при копировании не меняется номер строки.
Адреса ячеек в формуле вводятся щелчком указателя мыши по нужной ячейке. Для быстрого изменения типа ссылки после ввода адреса в формулу используется клавиша F4.
Задание 6. Создайте формулу, которая вычисляет сумму двух чисел из столбцов E и F и делит ее на число, содержащееся в ячейке B6, рис. 7.
Рекомендации к выполнению задания. 1. Введите в ячейку H2 формулу =(E2+F2)/ $B$6. Скопируйте формулу в ячейки H3:H6 и выявите различие в поведении относительных и абсолютных адресов.
2. Добавьте примечание к одной из ячеек, содержащей формулу.
3. Оформите три созданные маленькие таблицы, используя рамку и заливку.
2.6. Встроенные функции
В Excel существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры. При этом активируется средство автозаполнения формул, позволяющее по первым введенным буквам выбрать нужную функцию. После имени каждой функции в круглых скобках задаются аргументы. Аргументами могут быть адреса ячеек, имена диапазона ячеек, другие встроенные функции.
Имя функции в формуле можно вводить, вызывая Мастер функций (вкладка Формулы). Очень часто используется функция суммирования, поэтому она вынесена в виде кнопки (Автосумма) на вкладке Главная.
Полезной возможностью по работе с формулами является отображение всех формул на листе (Формулы ► Зависимости формул ► Показать формулы). После этого в ячейках вместо вычисленных значений будут показаны записанные формулы. Для возврата в обычный режим нужно еще раз нажать кнопку Показать формулы.
Если формула возвращает ошибочное значение, можно определить ячейку, которая вызывает ошибку. Для этого нужно активизировать команду Формулы ► Зависимости формул ► Проверка наличия ошибок ► Источник ошибок.
Для отладки формул существует средство вычисления формул (Формулы ► Зависимости формул ► Вычислить формулу), которое показывает пошаговое вычисление в сложных формулах.
З
A
B
C
1
x
y
z
2
-2
3
-1
4
0
5
1
…………………
Рис.
8. Шаблон таблицы
cos(x),
если x ≤ 0,
ln(x),
если x > 0;
y = {
z
|y|, если
y ≤ 0,
lg(y), если
y > 0.
Рекомендации к выполнению задания. 1. Выберите лист Функции1. Введите в ячейки A1, B1, C1 заголовки столбцов (рис. 10). Ячейки A2:A11 заполните последовательностью любых отрицательных и положительных чисел с шагом 1.
2. Установите указатель в ячейку B2. Вызовите диалоговое окно Мастер функций. В категории Логические выберите функцию Если. Откроется окно для ввода аргументов, содержание которых указано слева от полей. Введите аргументы в соответствии с формулой =ЕСЛИ(A2<=0;COS(A2);LN(A2)).
3. Вычислите таблицу значений функции y(x), скопировав формулу из ячейки B2 в ячейки B3:B11. Если формула возвращает ошибочное значение, определите ячейку, которая вызывает ошибку.
4. Аналогично проведите вычисление таблицы значений второй функции.
5. Отобразите все формулы на листе.
6. Назначьте построенной таблице внешние и внутренние границы.