Microsoft Excel 2010:Работа с формулами и функциями. Использование ссылок и имен...
Данная глава посвящена изучению понятий «формула» и «функция»- Вы познакомитесь с правилами синтаксиса при записи формул, использования в них ссылок и имен, а также узнаете, как создавать формулы с помощью мастера.
Построение формул
Чтобы производить какие-либо вычисления в таблице, необходимо научиться правильно строить формулы.
Формулой в Excel называется последовательность символов, которая начинается со знака равенства (=) и содержит вычисляемые элементы (операнды) и операторы: сложить (+), вычесть (-) и т.д.
Операндами формулы могут быть:
■ постоянные значения;
■ ссылки на ячейки (диапазон ячеек);
■ имена;
■ функции.
Для начала рассмотрим самое простое и наиболее часто используемое вычисление - подсчет итоговых значений. Например, в приведенной на рис. 23.1 таблице Косметика требуется заполнить итоговый столбец, значения которого рассчитать следующим образом: кол-во * цена.
Рис. 23.1. Подсчет итогов
Формулу удобно создавать вйачале для первой строки. Создать формулу в ячейке можно, двумя, способами: используя Строку формул или непосредственно в ячейке.
Чтобы ввести формулу через Строку формуя* следует:
1. Щелкнуть на ячейке ЕЗ. . .
2. Щелкнуть мышью в строке формул и ввести выражение: =C3*D3 (рис. 23.2).
Адреса ячеек удобно вводить с помощью щелчка левой кнопки мыши на соответствующей ячейке таблицы. Бели же это делается вручную, необходимо следить, чтобы в ссылке использовались латинские, а не русские буквы
Примечание. При составлении формулы целесообразно указывать не содержимое, а адреса тех ячеек, в которых содержатся данные. Тогда в случае изменения данных не нужно будет изменять саму формулу.
3. Нажать в строке формул кнопку Ввод или клавишу Enter
Рис. 23.2. Ввод формулы в ячейку
Чтобы вставить формулу непосредственно в ячейку, нужно;
1. Двойным щелчком поместить курсор в ячейку.
2. Ввести формулу.
3. Нажать клавишу Enter.
Чтобы заполнить остальные ячейки столбца Итого, руб., достаточно скопировать в них формулу с помощью маркера заполнения. При этом ссылки будут автоматически изменены соответствующим образом. В частности, для ячейки ЕЮ формула будет иметь вид (рис. 23.3): «C10*D10.
Рис. 23Л. Результат копирования формулы
Если данные оформлены в виде таблицы Excel (к диапазону был применен стиль таблицы), то для заполнения столбца формулами будет использован вычисляемый столбец. В таком столбце после ввода в любую ячейку формула автоматически применяется ко всем остальным ячейкам столбца. Таким образом, формулу достаточно ввести один раз, и команда копирования для других ячеек не понадобится. Добавляемые после этого строки автоматически включаются в такой столбец, и к ним применяется та же формула.
Теперь осталось в строке Всего подсчитать суммарные значения по каждому из столбцов. Это можно быстро осуществить с помощью автдсуммы:
1. Выделяем ячейку СИ.
2. На вкладке Главная в панели Редактирование нажимаем
кнопку г Сумма, после чего Excel автоматически создаст формулу для суммирования (рис. 23.4а).
3. Завершаем ввод нажатием клавиши Enter.
Аналогичным образом получаем суммарные значения в ячейках D11 и Е11 (рис. 23.46).
Отметим, что в данном случае формулу лучше не копировать из ячейки С16, поскольку форматы чисел в столбцах разные, соответственно после копирования в ячейках D11 и Е11 получим числа в том же формате, что и в ячейке СИ (т.е. без десятичных знаков).
Ссылка однозначно определяет ячейку или группу ячеек рабочего листа, указывая, в каких ячейках находятся данные, которые нужно применить в качестве аргументов формулы.
Ссылки на ячейки используют заголовки соответствующих строк и столбцов. В Excel столбцы помечены латинскими буквами (А, В, С,..., Z; АА, AB,..., AZ; ВА, ВВ и т.д.), а строки пронумерованы. Такие ссылки называются ссылками типа А1. Ссылаться можно как на отдельные ячейки, так и на диапазоны ячеек.
Когда в диапазон входят смежные ячейки, например, Al, A2 и A3 или А1, В1 и С1, он обозначается при помощи ссылок на первую и последнюю его ячейки,, между которыми ставится знак двоеточия. Например, сумму ячеек А1, А2, A3, А4 можно записать, используя функцию суммирования, следующим образом:
=СУММ(А1:А4)
В.табл. 23.1 приведены некоторые варианты ссылок на различные диапазоны.
Таблица 23.1 Примеры ссылок на диапазоны
Если диапазон содержит несмежные ячейки, между ними ставится точка с занятой. Например, чтобы просуммировать два диапазона А1:А4 и С1 :С4, следует ввести формулу:
=СУММ(А1:А4;С1:С4)
В ЕхсеГразличают относительные и абсолютные ссылки. ■ Относительные ссылки (вида А1) автоматически изменяются в случае копирования формулы из одной ячейки в другую и используются в новых формулах по умолчанию.
Примечание. При перемещении ячейки с формулой относительные ссылки не изменяются.
■ Абсолютные ссылки (вида $А$1) имеют, кроме имени столбца и номера строки, специальный символ - доллар ($), который фиксирует часть ссылки и оставляет ее неизменной при копировании этой формулы в другую ячейку. Обычно в таких ячейках содержатся значения, констант» используемых при 4 вычислениях.
Например, в таблице, приведенной на рис. 23.5а, .требуется заполнить столбец Цена, руб.. путем умножения соответствующих значений столбца Цена, $ на курс доллара, значение которого находится в ячейке D1. Для этого:
1. Устанавливаем курсор в ячейку D3 и нажимаем знак равенства
2. Щелкаем по ячейке D1, вводим знак умножения (*), а затем щелкаем по ячейке СЗ. В результате получим формулу: =D1*C3.
3. Теперь с помощью символа $ зафиксируем ссылку на ячейку D1, чтобы при копировании формулы в остальные ячейки столбца ссылка на эту ячейку не изменялась. Таким образом, формула примет вид: = 1*C3 (рис. 23.56).
Примечание. Чтобы изменить тип ссылки, достаточно в формуле установить курсор на ссылку, которую требуется изменить, и нажатием клавиши F4 выбрать требуемый тип ссылки.
4. Скопируем формулу в остальные ячейки столбца Цена, руб. Как видно в строке формул (рис. 23.5в), ссылка на ячейку столбца Цена, $ изменилась соответствующим образом, а на ячейку с курсом доллара - осталась неизменной.
Если требуется зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка (в частности, столбец А или строку 1), то ссылка примет вид $А1 или А$1 соответственно. Так, в приведенном выше примере можно вместо абсолютной ссылки 1 использовать ссылку D$l, т.к. копирование производится лишь по строкам, а столбец не изменяется.
В формулах можно ссылаться не только на ячейки внутри одного листа, но и использовать так называемые трехмерные ссылки, с помощью которых задавать диапазоны, включающие данные других листов. При этом между именем листа и ссылкой обязательно нужно ставить восклицательный знак (!).
Рис. 23.5. Использование абсолютных и относительных ссылок
Например, чтобы просуммировать данные, расположенные в ячейке А2 листа Лиспй и ячейке A3 листа ЛистЗ, нужно в результирующей ячейке ввести следующую формулу:
=Лист2!А2+ЛистЗ!АЗ
Для того чтобы сослаться на данные другой книги, используют внешние ссыпки, вид которых зависит от того, открыта или закрыта книга, данные из которой требуется подключить.
Если книга открыта, внешняя ссылка содержит имя файла в квадратных скобках, имя листа и саму ссылку. Например, чтобы просуммировать значение ячейки В1 текущего листа книги и значение той же ячейки из другой открытой книги Таблица!^ следует ввести формулу:
=В1+[Таблица2.х15х]Лист1!$В$1
Если книга закрыта, внешняя ссылка содержит полный путь:
Примечание, Если имя книги или листа содержит небуквенные знаки, его (или весь путь) следует заключить в апострофы.
Использование имен
В Excel существует довольно простой и понятный способ оперирования данными листа, который заключается в использовании имен - легко запоминающихся текстовых обозначений,, применяемых для ссылки на элементы рабочего листа.
Имя представляет собой ссылку на ячейку, диапазон ячеек, значение, формулу или объект. Имена удобно присваивать, когда ячейки часто используются в вычислениях.
Применение имен обеспечивает такие преимущества:
■ Формулы с именами, более понятны, чем формулы со ссылками на ячейки. Например, формула =Прибыль*Коэффициент гораздо понятнее, чем формула ~G9*S6.
■ При изменении структуры листа достаточно обновить ссылки только в определении имен, и все формулы, использующие эти имена, будут оперировать с корректными ссылками.
В Excel приняты следующие правила задания имен:
■ имя должно содержать не более 255 символов;
■ допускается использовать только буквы, цифры, а также подчеркивание О, косую черту (\) и точку (.);
нажимаем кнопку.
2. Откроется окно Создание имени, в котором по умолчанию будет задано Имя, Область действия и Диапазон (ячейка), ко торому присваивается имя. Назначаем диапазону имя Курс_доллара (б).
Примечание. Бслй задать локальную область действия имени, (например JIucml), то данное имя будет распознаваться только на указанном листе, а на остальных - нет. Чтобы его использовать на других листах, перед именем придется вставлять имя листа: JIucml !Курс__доллара.
3. Создание имени завершаем нажатием кнопки ОК в окне Со здание имени. Теперь при установке курсора в ячейку D1 в по ле имени будет отображаться имя Курс_доллара (в).
Теперь составим формулу перерасчета цен, используя созданное имя (рис. 23.7):
1. Устанавливаем курсор в ячейку D3 и вводим начало формулы =СЗ*.
2. Далее в формуле указываем в качестве ссылки имя Курс_доллара. Его можно ввести с клавиатуры либо воспользоваться списком имен кнопки Использовать в формуле на панели Определенные имена вкладки Формулы (а).
3. Имя будет автоматически добавлено в текст формулы: =СЗ*Курс_доллара (б).
4. Для завершения формулы нажимаем клавишу Enter.
Рис. 23.7. Использование имени в формуле
Работа с массивами
Иногда вместо нескольких формул, оперирующих отдельными значениями, удобно использовать одну формулу массива, которая оперирует множествами значений, называемых массивами аргументов, и возвращает один или множество результатов. .
Ввод формулы массива необходимо завершать нажатием комбинации клавиш Ctrl+Shift+Enter. В результате формула будет заключена в строке формул в фигурные скобки, которые отображаются лишь при выборе ячейки, а в режиме редактирования исчезают.
Примечание. Нельзя вводить фигурные скобки вручную, т.к. они будут восприняты как текст.
Рассмотрим, как можно с помощью формулы массива получить одно значение.
Допустим, для таблицы, приведенной на рис. 23.8, требуется определить общую сумму продаж без промежуточных расчетов. * Для этого необходимо выполнить следующие действия:
Рис. 23.8. Ячейка с вычисленной формулой массива
1. Выбрать ячейку, в которую требуется поместить формулу массива (011).
2. Ввести формулу =СУММ(СЗ:С10*ОЗ:О10). Данная формула умножает значения столбцов Кол-во и Цена, руб. для каждого наименования, а затем суммирует полученные результаты.
С помощью формулы массива можно упростить схему вычислений, заменив несколько отдельных формул одной формулой массива. Такая формула помещается не в одну ячейку, а сразу в диапазон ячеек -диапазон массива.
Например, чтобы в таблице, приведенной на рис. 23.9, получить итоговый столбец Всего, р., следует:
1. Выделить диапазон D2:D4.
2. Ввести формулу =B2:B4*C2:D4.
3. Нажать комбинацию клавиш Ctri+Shift+Enter.
Рис. 23.9. Получение множества значений в результате применения формулы массива
Изменять отдельные ячейки в диапазоне, содержащем формулу массива, нельзя. Для изменения формулы следует выделить весь диапазон массива, затем перейти в строку формул, внести изменения и нажать Ctrl+Shift+Enter.
Кроме того, нельзя добавлять или удалять строки или столбцы из диапазона, на который ссылается формула массива. При попытке это сделать Excel выдаст сообщение, о том, что нельзя изменить часть массива.
Если прочитаная статья из нашей обширной энциклопедия компьютера - "Microsoft Excel 2010:Работа с формулами и функциями. Использование ссылок и имен...", оказалась полезной или интересной, Вы можете поставить закладку в социальной сети или в своём блоге на данную страницу:
Так же Вы можете задать вопрос по статье через форму обратной связи, в сообщение обязательно указывайте название или ссылку на статью!
Обсудить статью на форуме