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

Знайте, что любая функция может выступать аргументом для какой-либо другой функции. Этот прием получил название вложение функции.

Примечания к ячейкам

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

Отображением примечаний управляют в настройках программы (Файл → Параметры) в разделе Дополнительно (подраздел Экран) с помощью переключателя Для ячеек с примечаниями показывать, который может принимать три положения:

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

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

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

Пример ячейки с примечанием изображен на рис. 3.10.

Рис. 3.10. Ячейка с примечанием

Для создания примечания щелкните на соответствующей ячейке правой кнопкой мыши и в открывшемся контекстном меню выберите команду Вставить примечание. После этого появится окно примечания, в котором по умолчанию будет отображаться имя пользователя (на рис. 3.10 имя пользователя – Alex). В данном окне с клавиатуры вводится текст примечания, который может быть абсолютно произвольным. Завершается создание примечания щелчком кнопкой мыши в любом месте рабочей области.

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

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

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

При необходимости можно быстро выделить все ячейки текущего рабочего листа, у которых имеются примечания, – для этого нужно нажать комбинацию клавиш Ctrl+Shift+O. Чтобы удалить примечания из всех ячеек рабочего листа, нужно выделить их нажатием комбинации клавиш Ctrl+Shift+O, затем щелкнуть правой кнопкой мыши на любой из этих ячеек и выполнить команду контекстного меню Удалить примечание.

Форматирование активной ячейки или диапазона

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

Для перехода в соответствующий режим предназначена команда контекстного меню Формат ячеек, вызываемая также нажатием комбинации клавиш Ctrl+1. При выполнении данной команды отображается окно, которое показано на рис. 3.11.

Рис. 3.11. Режим форматирования диапазонов и ячеек

Как видно на рисунке, данное окно состоит из нескольких вкладок: Число, Выравнивание, Шрифт, Граница, Заливка и Защита. Кратко остановимся на каждой из них.

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

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

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

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

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

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

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

Защита рабочего листа с бухгалтерскими данными от несанкционированного доступа

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

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

Рис. 3.12. Настройка защиты рабочего листа

В данном окне необходимо установить флажок Защитить лист и содержимое защищаемых ячеек (он установлен по умолчанию). Если он снят, то вы не сможете включить защиту, поскольку будет заблокирована кнопка ОК.

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

Рис. 3.12. Настройка защиты рабочего листа

В данном окне необходимо установить флажок Защитить лист и содержимое защищаемых ячеек (он установлен по умолчанию). Если он снят, то вы не сможете включить защиту, поскольку будет заблокирована кнопка ОК.

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

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

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

Режим защиты рабочего листа включается нажатием кнопки ОК. Кнопка Отмена предназначена для закрытия данного окна без включения защиты и сохранения выполненных в нем изменений.

Чтобы снять защиту с рабочего листа, выберите в контекстном меню, вызываемом нажатием правой кнопки мыши на ярлыке листа, команду Снять защиту с листа (она отображается только для защищенных листов вместо команды Защитить лист). При активизации данной команды открывается окно, в котором следует с клавиатуры ввести пароль, установленный при включении защиты рабочего листа, и нажать кнопку ОК. При вводе пароля не стоит забывать, что программа различает регистр символов (прописные либо строчные). Если введен неправильный пароль, то программа выдает соответствующее информационное сообщение.

Если при включении защиты пароль не вводился, то для снятия защиты с рабочего листа просто выполните команду Снять защиту с листа.

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

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

Необходимость подобных операций в бухгалтерской практике возникает часто, вот лишь несколько примеров:

• отдельное сложение сумм доходов и сумм расходов;

• раздельный учет выручки от реализации разных видов продукции;

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

• и т. д.

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

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

Предположим, мы имеем длинный список какихто чисел, находящихся в диапазоне А2:А100. С помощью условного форматирования (напомним, что переход к данному механизму делается на вкладке Главная, где в разделе Стили следует нажать кнопку Условное форматирование и выбрать подходящий режим работы) были выборочно помечены только те ячейки диапазона, в которых содержатся значения от 10 до 20. Сейчас перед нами стоит задача: необходимо сложить значения во всех ячейках, которые удовлетворяют данному условию, после чего выделить полученный результат с помощью условного форматирования. В данном случае не имеет значения, какое именно форматирование применено в ячейках – нужно лишь знать точный критерий, в соответствии с которым они будут отформатированы. В рассматриваемом примере таким критерием является значение, находящееся в диапазоне от 10 до 20.

Для суммирования значений ячеек, удовлетворяющих установленному критерию, удобно использовать функцию СУММЕСЛИ, однако в данном случае возможно указание лишь одного критерия. При наличии нескольких факторов проверки целесообразнее воспользоваться формулой массива.

Ее можно записать следующим образом:

Учтите, что при вводе формул массива нужно нажимать не клавишу Enter, а комбинацию клавиш Ctrl+Shift+Enter. Это необходимо для того, чтобы программа автоматически заключила формулу в фигурные скобки.

В итоге формула должна принять следующий вид:

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

Существует еще один способ решения поставленной задачи. Сущность его заключается в использовании дополнительного столбца (для примера возьмем столбец В) для ссылки на ячейки, находящиеся в столбце А. Смысл применения этих ссылок состоит в том, что они отобразят результат в столбце В только в том случае, если значение будет соответствовать заранее определенному условию (в нашем примере – находиться в диапазоне от 10 до 20).

Установите курсор в ячейку В1 и введите в нее формулу следующего вида:

Затем введите данную формулу в каждую ячейку столбца В до ячейки В100 включительно.

Быстро скопировать формулу в расположенные ниже ячейки можно следующим образом. Введите формулу в первую ячейку диапазона, выделите весь диапазон (включая эту ячейку), затем на вкладке Главная в разделе Редактирование нажмите кнопку Заполнить (название кнопки отображается в виде всплывающей подсказки при подведении к ней указателя мыши) и в открывшемся меню выберите команду Вниз.

Если столбец А у вас уже заполнен, то в столбце В вы увидите только значения, удовлетворяющие заданному условию (то есть находящиеся в диапазоне от 10 до 20).

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

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

Эта функция (она называется БДСУММ) является одной из функций баз данных. Чтобы на конкретном примере увидеть, каким образом она работает, используем тот же самый набор чисел, расположенных в диапазоне А2:А100, с которым мы уже работали ранее.

Выделите диапазон ячеек C1:D2 и присвойте ему имя SumCriteria (напомним, что имя диапазона вводится в специально предназначенном поле, которое находится слева от строки формул, см. выше раздел «Строка формул»). После этого установите курсор в ячейку С1 и введите в нее значение =$A$1 (иначе говоря, мы ввели в ячейку С1 ссылку на ячейку А1, которая является первой ячейкой рабочего листа). Теперь скопируйте данную ссылку в ячейку D1, которая находится в этом же диапазоне – в результате вы получите две копии заголовка столбца А. В дальнейшем эти созданные копии нам понадобятся в качестве заголовков условий функции БДСУММ (C1:D2) для диапазона, который мы назвали SumCriteria.