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

На следующем этапе в ячейку С2 следует ввести значение >10, а в ячейку D2 – значение <20. Как нетрудно догадаться, это условие, в соответствии с которым значения должны находиться в диапазоне от 10 до 20. Теперь установите курсор в ячейку, предназначенную для отображения результата, и введите в нее формулу следующего вида:

Функция БДСУММ является одним из самых эффективных приемов работы с ячейками, которые отвечают заранее установленному условию. Характерным отличием встроенных функций баз данных от массивов является то, что они были придуманы и разработаны специально для решения подобных задач и как никакой другой инструмент лучше всего для этого приспособлены. Поэтому даже тогда, когда вы работаете с внушительными объемами данных (в частности, когда этим функциям приходится ссылаться на большие диапазоны, причем одновременно используется немалое количество этих функций), это практически никак не сказывается на быстроте пересчета и на эффективности работы, чего никак нельзя сказать о формулах массива.

Быстрое преобразование чисел из текстового формата в числовой

Содержимое ячейки может внешне быть представлено в виде чисел, но на самом деле иметь текстовый формат. Наиболее характерный пример – когда данные импортируются из внешних источников (файл Word, отчет из бухгалтерской программы и т. д.). Возникающее несоответствие неудобно в первую очередь тем, что подобные числа трудно или вообще невозможно использовать в вычислениях. В данном разделе мы расскажем о том, как с помощью несложного приема можно быстро преобразовать формат чисел из текстового в «родной», числовой.

Напомним, что во всех версиях Excel значения с числовым форматом по умолчанию выравниваются по правому краю ячейки, а значения с текстовым форматом – по левому краю. Если у вас есть определенный диапазон с числовыми данными, которые представлены и в текстовом, и в числовом формате – в первую очередь необходимо определить, где какой формат. Для этого выделите этот диапазон, затем на вкладке Главная в правом нижнем углу раздела Выравнивание нажмите кнопку открытия окна форматирования ячеек и диапазонов (на этой кнопке изображена маленькая стрелка; при подведении указателя мыши отображается всплывающая подсказка Формат ячеек: выравнивание). В открывшемся окне на вкладке Выравнивание в поле по горизонтали из раскрывающегося списка выберите значение по значению (рис. 3.13), и нажмите кнопку ОК.

Рис. 3.13. Настройка выравнивания

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

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

• Скопируйте любую пустую ячейку в буфер обмена с помощью команды контекстного меню Копировать либо нажатием комбинации клавиш Ctrl+C.

• Выделите диапазон с данными, содержащий числа в текстовом формате.

• Нажмите правую кнопку мыши и в открывшемся контекстном меню выполните команду Специальная вставка.

• В открывшемся окне установите переключатель Вставить в положение значения, а переключатель Операция – в положение Сложить.

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

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

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

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

Допустим, у нас имеется какойто выделенный диапазон, первой ячейкой которого является D4, причем каждая ячейка включает в себя символ $ и некоторое число, после которого через пробел имеется имя человека. В частности, в подобном виде может быть представлена информация о заработной плате работников (например, $180.20 Иван). С помощью формулы, включающей в себя текстовые функции ЛЕВСИМВ и НАЙТИ, вы можете легко получить это число.

Результатом вычисления данной формулы будет значение $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, откройте вкладку Формулы и выберите пункт Определенные имена → Присвоить имя.

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

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

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

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

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

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

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

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

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

Расчет промежуточных итогов

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

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

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

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

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

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

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

• Добавить итоги по – в данном поле путем установки соответствующих флажков следует определить столбцы таблицы, по которым должны быть рассчитаны промежуточные итоги. Например, если в нашем примере в состав таблицы помимо столбца Наименование товара входят еще столбцы Количество и Цена, то целесообразно установить только флажок Количество (названия флажков аналогичны названиям столбцов таблицы), поскольку расчет промежуточных (да и общих) итогов по столбцам Цена и Наименование товара не имеет смысла.

• Заменить текущие итоги – данный флажок следует установить в случае необходимости замены имеющихся промежуточных итогов новыми. По умолчанию данный флажок установлен.

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

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

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

Нажатием кнопки ОК завершается процесс настройки расчета промежуточных итогов. Кнопка Отмена предназначена для выхода из данного режима без выполнения расчета.

Настройка доступа пользователей к рабочей книге

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

• обеспечение неприкосновенности конфиденциальной информации;

• защита данных от несанкционированного и неквалифицированного доступа и как следствие – сохранение ее целостности;

• предотвращение возможных потерь важной информации (возникающих, как правило, в результате ошибочно выполненных операций удаления и корректировки данных);

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

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

Рис. 3.15. Настройка параметров доступа

На вкладке Правка в поле Файл открыт следующими пользователями отображается список пользователей, работающих в данный момент с книгой. Для каждого пользователя отображается его имя, а также дата и время открытия рабочей книги данным пользователем. В верхней части вкладки расположен флажок Разрешить совместный доступ. Если он установлен, то текущая рабочая книга становится как бы общей, то есть в нее могут вносить изменения одновременно несколько пользователей (участников локальной сети), с последующим объединением всех выполненных изменений. Только при установленном данном флажке становятся доступными параметры, расположенные на вкладке Подробнее (см. рис. 3.15).

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

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