Домашний компьютер - Роман Кравцов

2. Щелкните на ячейке В10 и введите формулу = (С3-В3)/В3*100.

Знак равенства указывает программе Excel, что ячейка содержит формулу. СЗ и ВЗ – это ссылки на содержимое соответствующих ячеек. Если предположить, что таблица содержит объем продаж продукции различным клиентам, то (СЗ-ВЗ) – это прирост продаж для фирмы «Фантом» с января по февраль. Разделив полученную величину на ВЗ (продажи за январь), получаем относительный прирост продаж за месяц. Результат умножается на 100, что дает прирост в процентах. Как видите, в ячейки можно вводить простые, арифметические выражения со знаками +, -, *, / и скобками, определяющими последовательность выполнения операций.

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

Примечание

Чтобы результат выводился в процентах, не обязательно умножать его на 100, достаточно сменить формат данных, выделив ячейку и гцелкнув на кнопке Процентный формат панели инструментов Форматирование. На первый взгляд кажется, что в ячейки СЮ: F10 должна дублироваться формула ячейки В10, но это не так. Excel поддерживает механизм относительных ссылок на ячейки. Когда формула переносится на одну клетку вправо, точно так же смегцаются все ссылки этой формулы (это верно и для смешения формулы в любых других направлениях на любое число клеток). То есть в ячейке С11 появится формула = (D3-C3)/C3*100, в ячейке D10 – формула = (E3-D3)/D3*100 и т. д. Чтобы проверить это, щелкните на каждой ячейке и изучите содержимое строки формул. В итоге ячейки строки 10 будут содержать относительный прирост продаж компании «Фантом» по месяцам.

4. Выделите строки с третьей по седьмую.

5. Щелчком на кнопке Вырезать (Cut) панели инструментов Стандартная (Standard) вырежьте их содержимое.

6. Щелкните на ячейке А2 правой кнопкой мыши и выберите в контекстном меню команду Вставить. Данные таблицы переместятся вверх на одну строку. Теперь снова изучите формулы ячеек строки 10.

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

Примечание

Если необходимо, чтобы знак равенства в начале текста ячейки не воспринимался как индикатор формулы, а интерпретировался как символ, поставьте перед ним одинарную кавычку (‘).

Функции

Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С тремя из них – СУММ (SUM), СРЗНАЧ (AVERAGE) и СЧЕТ (COUNT) – вы уже познакомились. Чтобы научиться работать с другими функциями, выполните следующие шаги.

1. Щелкните на ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.

2. Щелкните на кнопке Вставка функции (Paste Function) панели инструментов Стандартная. Откроется окно диалога, показанное на рисунке 3.142. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке Категория (Select a Category) этого окна диалога, а затем пролистать список Выберите функцию (Select a Function Name). Выделив имя одной из функций, вы увидите ее описание в нижней части окна диалога. Excel делит все функции на следующие категории:

• Финансовые (Financial) – функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;

• Дата и время (Date & Time) – операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в таблице:

• Математические (Math & Trig) – математические и тригонометрические функции, некоторые из них приведены в таблице:

• Статистические (Statistical) – функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик.

• Ссылки и массивы (Lookup & Reference) – операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;

• Работа с базой данных (Database) – функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;

• Текстовые (Text) – функции для работы с текстовыми строками. Часть этих функций перечислена в следующей таблице:

• Логические (Logical) – шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение Истина или Ложь:

• Проверка свойств и значений (Information) – функции проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий.

В списке Категория (Function Category) есть также пункты Рекомендуемый перечень (Recommended), Полный алфавитный перечень (All) и 10 недавно использовавшихся (Most Recently Used), которые выводят соответственно список всех функций и 10 функций, применявшихся последними.

1. Выберите в списке Категория окна диалога вставки функций (рис. 3.142) пункт Статистические.

2. Выберите в списке Функция (Function Name) пункт МАКС.

3. Щелкните на кнопке ОК. Откроется окно ввода диапазона ячеек, показанное на рис. 3.143.

Некоторые функции, например SIN, имеют только один аргумент, поскольку нельзя вычислить синус сразу двух чисел. Однако многие функции, подобные МАКС, способны обрабатывать практически неограниченные массивы данных. Такие функции могут воспринимать до 30 аргументов, каждый из которых является числом или ссылкой на одну или несколько ячеек.

В качестве аргументов могут выступать также арифметические выражения и другие функции, возвращающие значения нужного типа.

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

4. Щелкните на кнопке в правой части поля Число 1 (Number 1). Окно диалога свернется в строку, открывая доступ к ячейкам листа.

5. Выделите все числовые ячейки строки Февраль. Обозначение соответствующего диапазона ячеек появится в строке свернутого окна диалога ввода аргументов (рис. 3.144).

6. Щелкните на кнопке строки аргумента. На экране снова развернется окно ввода аргументов.

7. Щелкните на кнопке в правой части поля Число 2 (Number 2).

8. Выделите числовые ячейки строки Март и снова щелкните на кнопке строки ввода аргумента. Поскольку вы ввели уже два аргумента, Excel автоматически добавит поле ввода третьего.

9. Введите в поле Число 3 (Number 3), как показано на рисунке 3.145. Это диапазон нужных ячеек строки Апрель. В окне диалога правее полей с аргументами демонстрируются их реальные значения. Ниже списка чисел отображается результат вычислений.

10. Щелкните на кнопке ОК. В ячейке 18 появится максимальное число из диапазона, записанного в столбцах С, D и Е. Сама формула появится в строке формул в верхней части окна Excel. Щелкните в этой строке. Три аргумента функции МАКС, задающие три диапазона ячеек, будут выделены разными цветами, а соответствующие группы ячеек листа Excel окажутся обведенными рамками соответствующих цветов (рис. 3.146).

Примечание

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

Формулы

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

Применение функций

До сих пор вы пользовались только функциями СУММ, СРЗНАЧ, СЧЕТ и МАКС. Давайте рассмотрим на примере некоторые функции из разряда текстовых и логических, а также функции работы с датой и временем.

1. В листе Формулы выделите и скопируйте ячейки B1:G1.

2. Разверните лист ЛистЗ (Sheet3).

3. Щелкните правой кнопкой мыши на ячейке А1 и выберите в контекстном меню команду Вставить.

4. Введите в ячейку АЗ формулу =ЛЕВСИМВ (А1;3) (=LEFT (А1; 3)). Эта формула возвращает три левых символа ячейки А1.

5. Перетащите угловой маркер выделения вправо, чтобы рамка охватила ячейки A3:F3. Теперь в третью строку выводятся сокращенные варианты названий месяцев из ячеек первой строки.