Алексей Гладкий - Компьютер для бухгалтера

Результатом вычисления данной формулы будет значение $180.20. Но учтите, что это значение будет представлено в текстовом формате, следовательно, в ячейке оно будет выровнено по левому краю.

Однако путем несложной модификации данной формулы вы можете добиться того, чтобы результат выводился именно в числовом формате. Для этого достаточно добавить к формуле 0 так, как показано ниже.

В результате значение будет представлено в числовом формате. После этого вам останется лишь отформатировать ячейку.

Механизм имен удобно использовать не только для того, чтобы ссылаться на данные по имени. Его также можно задействовать для постоянных значений либо формул; это особенно актуально при создании пользовательских функций средствами Visual Basic for Application.

Допустим, в текущей книге Excel вы используете для расчета налоговых отчислений ставку 10 %. Следовательно, во всех формулах, где используется данная ставка, необходимо будет вводить значение 10 % или 0,1. Однако вместо этого удобнее использовать слово TaxRate – и программа сама подставит значение 0,1, которое будет соответствовать данному имени. Для этого:

• откройте вкладку Формулы, в разделе Определенные именанажмите кнопку Присвоить имя;

• в открывшемся окне в поле Имя введите значение TaxRate, а в поле Диапазон – значение =0,1;

• нажмите кнопку ОК.

В результате при создании формул вы можете вместо значения 0,1 или 10 % использовать слово TaxRate.

Главным достоинством данного метода является то, что при изменении ставки налога вам достаточно будет не менять все формулы, а просто на вкладке Формулыв разделе Определенные именанажать кнопку Присвоить имяи в открывшемся окне создания и редактирования имен для имени TaxRate изменить его значение так, как требуется.

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

• На текущем рабочем листе установите курсор в ячейку А11, затем вызовите контекстное меню и выполните команду Имя диапазона.

• В открывшемся окне в поле Имя введите значение Total, а в поле Диапазон – формулу =СУММ(А1:А10), после чего нажмите кнопку ОК.

• В любом столбце начиная с первой строки введите 10 произвольных чисел.

• В этом же столбце установите курсор в строку 11 и введите в нее формулу =Total.

В результате выполненных действий вы получите сумму только что введенных значений.

Вот усовершенствованный вариант данного способа. Сущность его заключается в том, что вы создадите именованную формулу, которая будет использовать все ячейки, расположенные непосредственно над той строкой, в которой введено значение =Total. Для этого выполните перечисленные ниже действия.

• Установите курсор в ячейку В11, откройте вкладку Формулыи выберите пункт Определенные имена → Присвоить имя.

• Нажмите на имени Total.

• Посмотрите значение поля Диапазон: в нем должна отобразиться формула =СУММ(В1:В10). Таким образом создаются именованные формулы. Иначе говоря, так как абсолютные ссылки на столбцы для исходного имени Total не указаны, то формула всегда будет ссылаться именно на тот столбец, в котором она находится.

• В поле Диапазонизмените формулу так, чтобы она приняла вид =СУММ(В$1:В10).

Теперь в любой строке (за исключением первой) произвольного столбца введите значение =Total – вы получите автоматически рассчитанную сумму значений всех ячеек, которые находятся выше текущей ячейки независимо от количества строк.

Данный эффект достигается за счет того, что вы сделали ссылку на строку 1 абсолютной, а на ячейку В10оставили относительную ссылку, а такая ссылка все время будет указывать на ячейку, находящуюся непосредственно над строкой, содержащей именованную формулу =Total.

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

Чтобы расчет промежуточных итогов с помощью данного механизма был корректен, необходимо соблюдение следующих условий: первая строка таблицы должна содержать названия столбцов, а остальные строки – однотипные данные. Кроме этого, в таблице не должно быть пустых строк либо столбцов.

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

Рис. 3.14.Настройка расчета промежуточных итогов

В данном окне определяются значения перечисленных ниже параметров.

• При каждом изменении в – в данном поле из раскрывающегося списка (в список включены названия всех столбцов таблицы) нужно выбрать название столбца таблицы, на основании данных которого будет делаться вывод о необходимости добавления строки промежуточных итогов. Чтобы было понятно, каким образом обрабатывается значение данного поля, приведем конкретный пример. Допустим, что нужный столбец называется Наименование товара; первые три позиции в нем занимает товар Брюки, следующие четыре позиции – Туфли, следующие две позиции – Майки(все однотипные позиции различаются только по цене). Если в окне настройки расчета (см. рис. 3.14) в поле При каждом изменении в выбрать значение Наименование, то в таблицу будут добавлены строки с итоговыми данными отдельно по всем брюкам, всем туфлям и всем майкам.