Проведение вычислений

Рабочие книги Microsoft Excel предоставляют возможность хранения и организации ваших данных, однако возможности Excel гораздо шире. Одна из важных задач, которую вы можете решать, работая в Excel - вычисление суммы значений в ячейках. Вы также можете проводить другие операции с выбранными данными, например, вычислять максимальное или минимальное значения для группы ячеек. Поиск максимального из значений может помочь вам определить, например, самого продуктивного продавца, выявить категории товаров, на которые следовало бы обратить больше внимания, или поставщиков, которые делают вам самые выгодные предложения. Помимо хранения информации, Excel предоставляет возможность поиска необходимой информации. Также, если вы допустили ошибку, вы быстро найдете и исправите ее.

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

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

Присвоение названий группе данных

Когда вы работаете с большими объемами информации, желательно идентифицировать группы ячеек, которые содержат соответствующую информацию. Например, на рисунке ниже ячейки с С2 по С6 содержат цены на товары, заказанные покупателем.

Вместо того чтобы указывать ссылки на ячейки каждый раз, когда вам нужно обратиться к информации в них, вы можете объединить эти ячейки в диапазон (или названный диапазон ). Например, вы можете объединить товары из предыдущего рисунка в диапазон, названный "ЗаказанныеТовары1". Если вам нужно использовать данные из этого диапазона в вычислениях, вы можете просто ввести имя диапазона вместо того, чтобы указывать ссылку на каждую ячейку.

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

В данном случае, вы открываете диалоговое окно Создать имена (Create Name), выбрав в меню Вставка (Insert) подменю Имя (Name) и щелкнув на команде Создать (Create). В диалоговом окне Создать имена (Create Name) вы можете создать названный диапазон, присвоив ему в качестве имени заголовок (верхнюю ячейку) столбца. Вы также можете создавать и удалять диапазоны через диалоговое окно Присвоение имени (Define Name), открыть которое можно, указав на пункт Имя (Name) в меню Вставка (Insert) и выбрав Присвоить (Define).

Наконец, можно создать диапазон, выбрав ячейки, которые вы хотите объединить в диапазон, и введя в строке Имя (Name) название диапазона. Вы можете получить список диапазонов, созданных на листе, щелкнув в этой строке на кнопке списка.

В этом упражнении вы создадите диапазоны, чтобы адресовать ссылки на группы ячеек.

На панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open).

Перейдите в папку Chap07 и дважды щелкните на файле NameRange.xls. Файл nameRange.xls откроется.

Перейдите к листу Инструменты.

Щелкните на ячейке С3 и перетащите указатель на ячейку С18. Выбранные ячейки выделятся цветом.

В меню Вставка (Insert) укажите на пункт Имя (Name) и затем выберите Создать (Create). Откроется диалоговое окно Создать имена (Create names).

Отметьте пункт В строке выше (Top Row).

Нажмите OK. Excel присвоит диапазону ячеек имя "Цена".

В нижнем левом углу окна рабочей книги щелкните на ярлычке листа Поставки. Появится лист Поставки.

Щелкните на ячейке С4 и перетащите указатель на ячейку С29.

В меню Вставка (Insert) укажите на пункт Имя (Name) и выберите Присвоить (Define). Откроется диалоговое окно Присвоение имени (Define name).

В строке Имя (Names in Workbook) введите ЦеныНаПоставки и нажмите ОК. Excel присвоит имя "ЦеныНаПоставки" диапазону ячеек, и диалоговое окно Присвоение имени (Define Name) закроется.

В левом нижнем углу окна рабочей книги щелкните на ярлычке листа Фурнитура. Появится рабочий лист Фурнитура.

Щелкните на ячейке С4 и перетащите указатель на ячейку С18.

Щелкните на строке Имя (Name). Содержимое строки Имя (Name) выделится.

Введите СтоимостьФурнитуры и нажмиnе (Enter). Excel присвоит диапазону ячеек имя "СтоимостьФурнитуры".

В меню Вставка (Insert) укажите на пункт Имя (Name) и затем выберите Присвоить (Define). Откроется диалоговое окно Присвоение имени (Define Name).

В нижней области диалогового окна щелкните на строке "Цена". В поле Имя (Names in workbook) появится слово "Цена".

В поле Имя (Names in workbook) удалите слово Цена, введите СтоимостьИнструментов и нажмите ОК. Диалоговое окно Присвоение имени (Define Name) закроется.

На панели инструментов Стандартная нажмите кнопку Сохранить (Save).

Нажмите кнопку . Файл NameRange.xls закроется.

Создание формул для вычисления значений

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

Чтобы написать формулу Excel, начните вводить данные в ячейку со знака равенства, тогда они будут интерпретироваться как выражение для вычисления, а не текст. После знака равенства вы вводите формулу. Например, вы можете найти сумму значений в ячейках С2 и С3 с помощью формулы =С2+С3. После того как вы ввели формулу в ячейку, вы можете проверить ее, щелкнув на ячейке и отредактировав ее содержимое в строке формул. Например, вы можете заменить эту формулу на =C3-C2, для вычисления разности между содержимым ячеек С2 и С3.

Ввод ссылок на 15 или 20 ячеек может показаться утомительным, однако в Excel легко работать со сложными вычислениями. Для задания нового вычисления выберите пункт Функция (Function) в меню Вставка (Insert). Откроется диалоговое окно Мастер функций (Insert Function) со списком функций, или предопределенных формул, из которого вы можете выбрать нужную вам функцию.

В следующей таблице описаны наиболее часто используемые функции.

Вы также можете использовать две другие функции, ТДАТА() [NOW()] и ПЛТ() [PMT()]. Функция ТДАТА() возвращает время, когда рабочая книга была открыта, поэтому значение функции будет изменяться каждый раз, когда будет открываться рабочая книга. Правильная запись функции выглядит так: =ТДАТА(); чтобы обновить текущее время и дату, просто сохраните работу, закройте и снова откройте документ. Функция ПЛТ() немного сложнее. Она вычисляет сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Чтобы произвести с ее помощью вычисления, функции требуется задать ставку, количество месяцев платежей и стартовый баланс. Элементы, вводимые в функцию, называются аргументами и должны быть введены в определенном порядке. Этот порядок выглядит так: ПЛТ(ставка;кпер;пс;бс;тип). В следующей таблице приведены описания каждого аргумента функции ПЛТ.

Если вы взяли взаймы $20.000 под 8-процентную ставку и возвращаете кредит в течение 24-х месяцев, вы можете использовать функцию ПЛТ() для определения размера ежемесячных выплат. В этом случае, функцию следует задавать таким образом: =ПЛТ(8%/12,24,20000); функция возвратит величину ежемесячных выплат в размере $904.55.

Вы также можете использовать в формулах имена любых диапазонов ячеек. Например, если имя диапазона "Заказ1" ссылается на ячейки с С2 по С6, вы можете вычислить среднее значение ячеек с С2 по С6 по формуле =СРЗНАЧ(Заказ1) [AVERAGE(Заказ1)]. Если вы хотите включить в формулу область смежных ячеек, но еще не определили эти ячейки как диапазон, можете щелкнуть на первой ячейке диапазона и перетащить указатель на последнюю ячейку. Если ячейки не смежные, нажмите и удерживайте (Ctrl) и щелкните на нужных ячейках. В обоих случаях, когда вы отпустите кнопку мыши, ссылки на выбранные вами ячейки появятся в формуле.

Формулы также могут быть использованы для вывода сообщений при определенных условиях. Например, Кэтрин Тернер, владелец компании "Все для сада", предоставляет бесплатный экземпляр журнала о садоводстве покупателям, сделавшим покупки на сумму более $150. Такой тип формул называется условной формулой и использует функцию ЕСЛИ (IF). Чтобы написать условную формулу, щелкните на ячейке, которая будет содержать формулу и откройте диалоговое окно вставки функции. В диалоговом окне выберите функцию ЕСЛИ из списка доступных функций и нажмите ОК. Откроется диалоговое окно Аргументы функции (Function Arguments).

При работе с функцией ЕСЛИ, диалоговое окно Аргументы функции (Function Arguments) содержит три поля: Лог_выражение (Logical test), Значение_если_истина (Value_if_true) и Значение_если_ложь (Value_if_false). В строку Лог_выражение (Logical test) вводится условие, которое вы хотите проверять. Для проверки, превышает ли сумма заказа $150, выражение будет выглядеть так: СУММ(Заказ1)>150.

Теперь вам нужно сделать так, чтобы Excel отображал сообщения, указывающие на то, должен ли покупатель получать бесплатный экземпляр журнала. Чтобы выводить сообщения с помощью функции ЕСЛИ, введите эти сообщения в строках Значение_если_истина (Value_if_true) или Значение_если_ложь (Value_if_false). В данном случае вы можете ввести в строке Значение_если_истина (Value_if_true) "Вы получаете бесплатный журнал" и "Спасибо за покупку" в строке Значение_если_ложь (Value_if_false).

Когда вы создали формулу, вы можете скопировать ее в буфер и вставить в другую ячейку. После этого Excel попытается изменить формулу так, чтобы она работала в новых ячейках. В качестве примера на этом рисунке ячейка D8 содержит формулу =СУММ(С2:С6):

Щелкните на ячейке D8, скопируйте содержимое в буфер и вставьте результат в ячейку D16; в ячейке D16 появится выражение =СУММ(С10:С14). Excel изменит формулу так, что она будет применима к ячейкам в этой области листа! Excel использует в формулах относительную ссылку, или ссылку, которая изменяется при копировании формулы в другую ячейку. В относительных ссылках указывается только строка и столбец ячейки.

Если вы хотите, чтобы ссылка на ячейку оставалась неизменной при копировании в другую ячейку, вы можете использовать абсолютную ссылку. Чтобы сделать ссылку на ячейку абсолютной, нужно ввести значок $ перед номером строки и перед номером столбца. Например, для того чтобы формула в ячейке D16 выводила сумму значений в ячейках с С10 по С14, независимо от того, в какой ячейке она находится, следует ввести формулу =СУММ($C$10:$C$14).