Excel 2007: формулы без проблем
 | Завершая тему работы с формулами, нам осталось разобраться в трех моментах: как быстро строить длинные выражения, как использовать функции Excel и как контролировать вычисления. Эти вопросы мы и рассмотрим в нашей статье. Причем главное внимание теперь уделим не основам, а эффективным приемам работы с формулами. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
При разработке более-менее сложных формул нам, скорее всего, понадобятся встроенные функции Excel. Поэтому с них мы и начнем. То есть порядок рассмотрения материала я предлагаю другой: вначале мы поработаем с функциями и только затем перейдем к приемам создания сложных формул.
Использование
встроенных функций
Встроенные функции — очень важная часть Excel. Они нужны для выполнения различных вычислительных операций. С помощью функций можно, например, посчитать сумму ячеек в блоке, среднее арифметическое, округлить результат и многое другое. Библиотека Excel 2007 содержит около 400 встроенных функций. Все они разделены на категории: финансовые, математические, текстовые, логические, даты и времени, статистические, функции работы со ссылками и массивами, работы с базой данных, функции проверки свойств и значений и т. д. Могут быть и другие категории. Сделано это для удобства поиска функций.
Любая функция имеет уникальное имя. При обращении к функции ей нужно сообщить параметры. Например, в формуле «
=Сумм(A1:A9;B1:B5)» функции с именем «Сумм» передаются два параметра: адреса блоков ячеек «A1:A9» и «B1:B5», значения в которых нужно просуммировать. После обращения к ней функция возвращает результат — число, текстовую строку или логическое значение. Некоторые функции возвращают в качестве результата ссылку на ячейку рабочего листа.Параметры функции могут быть числовыми, логическими, текстовыми. В качестве параметров можно указывать адреса ячеек (или блока) электронной таблицы. В этом случае функция обрабатывает содержимое ячеек. Например, формула «
=Сумм(А1:А5)» суммирует содержимое ячеек «А1», «А2», «А3», «А4», «А5», формула «=СрЗнач(А1:А5)» находит среднее арифметическое значений в этих ячейках. Результатом работы обеих функций будет число.В Excel можно использовать вложенные функции. В этом случае первой обрабатывается функция, стоящая внутри выражения. Ее результат является параметром для внешней функции. Разумеется, результат и параметры обеих функций должны быть согласованы: если для внешней функции в качестве параметра нужно указать число, то и вложенная функция должна возвратить число. Например, формула «
=Округл(СрЗнач(H4:H8);2)» допустима. Она вычисляет среднее арифметическое блока «H4:H8» (возвращает число) и округляет полученный результат до двух знаков после запятой.Количество параметров у разных функций может отличаться. Если параметров несколько, они отделяются друг от друга символом «
;» (точка с запятой). Например, функция округления «Округл» имеет два параметра: число, которое требуется округлить, и точность округления. Формула «=Округл(10,125;2)» округлит число «10,125» до двух знаков, т. е. до «10,13». Функция извлечения квадратного корня «Корень» имеет один параметр — число. Формула «=Корень(9)» вернет в ячейку значение «3».Список параметров функции может быт пустым. Например, функция «
Сегодня()» возвращает значение текущей даты. Исходя из логики ее действий никакие параметры этой функции не нужны.Работа с функциями выполняется в разделе «
Формулы» главного меню. В нем есть специальная группа иконок с названием «Библиотека функций» (рис. 1). Всего иконок в этой группе десять. Первая слева называется «Вставить функцию». Щелчок по этой иконке вызывает специальный инструмент — Мастер функций. Он позволяет быстро и безошибочно внедрять функции в формулы Excel: вы заполняете бланк с параметрами, остальное Мастер сделает сам. Правее иконки «Вставить функцию» расположены еще девять элементов: «Автосумма», «Недавно использовались», «Финансовые», «Логические», «Текстовые», «Дата и время», «Ссылки и массивы», «Математические», «Другие функции». Эти иконки связаны с определенной категорией функций. Каждая иконка представляет собой раскрывающийся список. Щелкаем левой кнопкой на «Автосумма». Раскроется список, как на рис. 2. В верхней части списка есть перечень функций для выбранной категории (в данном случае их пять). Щелчок на любой строке списка вызывает окно для заполнения параметров конкретной функции. Последний элемент «Другие функции…» начинает работу с Мастером. 
Мастер функций — наиболее универсальный инструмент для работы с ними. Пользоваться им очень просто, вот как это сделать:
1) становимся в ячейку, куда нужно ввести функцию;
2) щелкаем на иконке «
Вставить функцию» или нажимаем кнопку «fx» (слева от строки формул);3) появится окно со списком функций (рис. 3). Находим нужную функцию (например, «
СуммЕслиМн»). Двойным щелчком вызываем ее или щелкаем на кнопке «ОК»; 
4) раскроется окно с параметрами функции (рис. 4);
5) заполняем параметры, нажимаем «
ОК» или клавишу «Enter».Выбор функции осуществляется из списка «
Выберите функцию» в средней части окна на рис. 3. Состав этого списка зависит от категории функции в одноименном поле «Категория». Например, если в этом поле выбрать значение «Финансовые», список будет ограничен только функциями данного типа.В поле «
Поиск функции» можно указать ее приблизительное название и воспользоваться кнопкой «Найти» (или нажать клавишу «Enter»). Так удобно находить функции схожего действия из разных категорий. Например, для обычного суммирования (функция «Сумм()», категория «Математические»), для суммирования в базах данных (функция «БдСумм()» категория «Работа с базой данных») и т. д.Складывается впечатление, что при таком поиске Excel анализирует не только имена, но и краткое описание функций. Например, если в поле «
Поиск функции» написать «Сумм», то в отобранный список попадет и функция «Счет», которая считает количество значений в заданном списке. Разумеется, в списке будут присутствовать «Сумм()», «СуммЕсли()», «СуммПроизв()», «БДСумм()» и другие функции, которые имеют отношение к операциям суммирования. При этом в поле категории Excel автоматически поставит значение «Рекомендуется».Важно!
Внутри списка функций удобно осуществлять поиск по первым буквам имени. Например, в режиме просмотра полного списка достаточно набрать на клавиатуре русскую букву «С» — и курсор мгновенно переместиться к функции «Сегодня()». Сочетание «Су» установит курсор на функцию «Сумм()» и т. д. Регистр символов может быть любым.После выбора функции Excel предложит ввести ее параметры. Их заполняют в специальном окне (рис. 4). Его вид зависит от типа функции, т. е. от количества ее параметров. Рассмотрим для примера работу с функцией «
СуммЕслиМн()». Она позволяет выборочно суммировать значения из таблицы, анализируя при этом несколько условий. У функции как минимум три параметра: диапазон суммирования (блок на рабочем листе), диапазон для проверки условия и собственно значение для проверки. Все три параметра представлены в окне на рис. 4. Для каждого параметра предусмотрено свое поле. Обязательные параметры выделены жирным шрифтом. В нашем случае — это первые три элемента. Заполняем их значениями и нажимаем кнопку «ОК» или клавишу «Enter». Excel построит функцию и внедрит ее в ячейку или в формулу.Функция «
СуммЕслиМн()» может проверять не одно, а несколько условий. Поэтому количество полей в окне параметров может динамически изменяться. Как только мы заполним «Диапазон_суммирования», «Диапазон_условия1» и «Условие1», Excel сразу же добавит в окне поля «Диапазон_условия2», «Условие2» и т. д.В нижней части окна можно прочитать краткое описание работы функции. Чтобы получить подробную справку о назначении, параметрах и примере использования, нужно щелкнуть на ссылке «
Справка по этой функции» (в левой нижней части окна). Перемещаясь по отдельным полям с параметрами, в нижней части окна можно прочитать комментарии об их назначении.Совет!
Работая с Мастером функций, подставляйте адреса ячеек и блоков в поле параметров щелчком мыши на рабочем листе точно так, как это делается при создании формул.Использование Мастера функций дает массу преимуществ. Вы никогда не ошибетесь в имени функции. Excel правильно сформирует список параметров и расставит скобки. Выбирая адреса методом навигации по рабочему листу, вы
гарантированно введете правильные значения. Есть и другие преимущества. Многие функции в локализованной версии Excel пишутся на русском языке. В то же время адреса ячеек и блоков нужно вводить в английской нотации. При использовании Мастера функций вам не придется постоянно переключать раскладку клавиатуры.Совет
Обязательно ознакомьтесь со списком функций Excel 2007. Многие из них очень полезны для бухгалтера. Чем шире будет ваш кругозор в этом вопросе, тем более сложные задачи вы сможете решать.В таблице приведен список функций, которые, на мой взгляд, достойны первоочередного внимания для бухгалтера. Многие из них мы будем использовать в наших примерах.
| Функция | Назначение |
| 1 | 2 |
| Сумм(арг1; арг2;…;арг n) | Суммирует аргументы, которыми могут быть числа, адреса ячеек и блоков |
| СуммЕсли(Блок1; Условие; Блок2) | Выборочно суммирует ячейки по условию. Параметры «Блок1» и «Блок2» — это адреса блоков таблицы, параметр «Условие» — текстовая строка с условием. Обязательными являются два первых параметра. Функция анализирует значения в блоке «Блок1» и проверяет их на условие «Условие». Если условие выполняется, она накапливает сумму из соответствующих значений «Блок2». Если параметр «Блок2» опущен, сумма образуется из значений «Блок1». «СуммЕсли» — очень ценная функция для бухгалтера. Она позволяет, например, выборочно просуммировать часть ячеек блока, которые помечены в отдельной колонке каким-то символом (или словом) |
| СуммЕслиМн(БлокСумм; БлокУсл1; Условие1; БлокУсл2; Условие2…) | Это новая функция в Excel 2007, расширенный вариант «СуммЕсли()». Она накапливает сумму из диапазона ячеек, анализируя при этом несколько условий. Они могут быть заданы в виде числа, выражения, ссылки на ячейку или текста. Обязательными являются первые три параметра. В этом случае функция проверяет ячейки в блоке «БлокУсл1» и сравнивает их со значением «Условие1». Если результат проверки «Истина» (условие выполняется), она накапливает сумму из блока «БлокСумм». У «СуммЕслиМн()» можно указать более трех параметров, введя дополнительные условия проверки. Тогда функция будет проверять ячейки из «БлокУсл2» и сравнивать их со значением «Условие2» и т. д. Суммирование выполняется по условию «И». То есть в том случае, когда ВСЕ сравнения возвращают результат «Истина». Excel 2007 допускает ввести 127 условий |
| СуммПроизв(Блок1;…; Блок n) | Находит сумму произведений значений в указанных блоках. Например, формула «=СуммПроизв (D2:D4;E2:E4)» поэлементно перемножит ячейки в указанных блоках и затем просуммирует результат. Фактически получим результат: «=D2*E2+D3*E3+D4*E4» |
| Счет(Блок1; Блок2;…;Блок n) | Считает количество чисел (именно чисел!) в указанных блоках |
| СчетЕсли(Блок1; Условие) | Считает количество непустых ячеек в блоке, удовлетворяющих условию «Условие». Очень похожа на функцию «СуммЕсли» |
| СчетЗ(Блок1; Блок2;…; Блок n) | Считает количество значений и непустых ячеек в блоках |
| СчитатьПустоты(Блок) | Считает количество пустых значений в блоке |
| Если(Условие; Выражение1; Выражение2) | Анализирует значение выражения «Условие». Если оно истинно, функция возвращает значение «Выражение1». Иначе она возвращает значение «Выражение2». Это единственная функция, которая позволяет организовать разветвления в вычислениях. Широко применяется для контроля данных. Расширить диапазон обрабатываемых условий можно логическими функциями «И()», «ИЛИ()», «НЕ()» |
| Макс(Блок1; Блок2;…;Блокn) | Находит максимальное значение в указанных блоках ячеек |
| Мин(Блок1; Блок2;…;Блокn) | Находит минимальное значение в указанных блоках ячеек |
| СрЗнач(Число1, Число2,…) | Вычисляет среднее арифметическое аргументов. Параметры могут быть числами, именованными диапазонами, ссылками на ячейки. Дополнительные возможности для определения среднего дают функции «СрЗначА()» (параметрами могут быть как числа, так и логические выражения), «СрЗначЕсли()» (определяет среднее по условию), «СрЗначЕслиМн()» (вычисляет среднее по набору условий) |
| Округл(Число; КолРазр) | Округляет число до указанного количества разрядов. Вместо числа можно указать адрес ячейки или формулу. Дополнительные способы округления предоставляют функции «ОкруглВверх()», «ОкруглВниз()», «ОкруглТ()» |
| Порог(Значение; Предел) | Проверяет, превышает ли указанное «Значение» заданный «Предел». Параметры можно вводить в виде ссылок на ячейки. Функция полезна при контроле значений и результатов вычислений в таблице |
| Год(Дата), Месяц(Дата), День(Дата) | Эти функции выделяют из даты номер года, месяца или дня. Например, формула «=Год(“01/04/2009”)» выдаст число «2009», формула «Месяц(“01/04/2009”)» вернет «4», формула «День(“01/04/2009”)» — число «1». Кавычки в данном случае обязательны. В качестве параметра можно указать ссылку на ячейку, в которой записана дата |
| Остат(Число; Делитель) | Возвращает остаток от деления параметра «Число» на «Делитель» |
| Сегодня() | Возвращает значение текущей даты |
| Римское(Число; Форма) | Преобразует арабское число в римское. Результат представлен в текстовой форме. Функция полезна при оформлении некоторых отчетов. Первый параметр — число для преобразования. Второй параметр — способ записи (необязательный). Может принимать значения «1» — классическая форма, «2» — наглядная, «3» — развернутая, «4» — упрощенная и т. д. Параметр можно оставлять по умолчанию |
| Строчное(Текст) | Делает все буквы в строке «Текст» строчными. Функция полезна при оформлении отчетов |
| Прописн(Текст) | Делает все буквы в строке «Текст» прописными |
| СжПробелы(Текст) | Удаляет из текста пробелы, оставляя одиночный пробел между словами. Функцию удобно применять при оформлении документов и при работе с базами данных |
| Сцепить(Текст1; Текст2;…) | Объединяет несколько текстовых строк в одну. Удобна при формировании составных заголовков и подписей в документах |
| Текст(Число; Формат) | Преобразует значение параметра «Число» в текст. Вид преобразования определяет строка «Формат». Используется при преобразовании даты в текстовое представление |
Подробные сведения о назначении параметров функций можно отыскать в справочной системе.
Создание сложных формул
Умение работать со сложными формулами — важнейший показатель уровня пользователя Excel. Чтобы быстро и безошибочно создать длинную формулу, нужно помнить четыре простых, но важных правила:
1. Адреса в формулу подставляйте, выбирая их на рабочем листе.
2. Функции стройте с использованием Мастера функций. Это гарантированно избавит от синтаксических ошибок.
3. Формула — это обычный текст, работа с формулами ничем не отличается от редактирования текстовой строки. При написании текста формул можно (и нужно!) использовать буфер обмена.
4. Длинную формулу создавайте по частям в отдельных ячейках, а затем объединяйте их в одну.
Рассмотрим применение этих правил на практике.
Пример 1.
Вложенная «Если»Начнем с несколько школьного, но показательного примера. В ячейку «
A1» я введу число в диапазоне от «1» до «5». В ячейку «B1» нужно ввести формулу, которая напишет это число текстом. Для решения задачи нужно сформировать формулу из пяти вложенных функций «Если()» — по одной для каждого случая. Быстро написать формулу можно так:1) становимся в ячейку «
B1», вызываем меню «Формулы», щелкаем на иконке Мастера функций;2) в окне Мастера (рис. 3) выбираем категорию «
Полный алфавитный перечень», переходим в поле со списком функций, печатаем «Ес» (на русском регистре);3) указатель в списке автоматически остановится на функции «
Если()», щелкаем на ней;4) появится окно параметров, заполняем их для первого случая, как показано на рис. 5. При этом ничего не печатаем, все адреса выбираем по рабочему листу! Например, чтобы построить условие проверки (первый параметр), щелкаем на ячейке «
A1», ее адрес появится в поле «Лог_выражение». Допечатываем строку «=1» и переходим ко второму параметру; 
5) здесь вводим текст «
Один». Это — результат работы формулы, когда в «А1» записана «1»;6) значение третьего параметра мы пока не знаем, печатаем любой символ, например «
1»;7) закончив формирование функции, нажимаем «
ОК». Мы получили выражение «=Если(A1=1;"Один";1)». Теперь нужно расширить ее для всех пяти случаев;8) становимся на ячейку «
B1». Нажимаем «F2», попадаем в режим редактирования формулы;9) находясь в режиме редактирования, выделяем
текст(!) формулы без символа «=». Щелкаем на выделенном блоке правой кнопкой. Выбираем из контекстного меню «Копировать». Текст формулы попадет в буфер обмена;10) стираем третий параметр в формуле (символ «
1»), вставляем на его место содержимое буфера обмена. В результате получим «=Если(A1=1;"Один";Если(A1=1;"Один";1))». Корректируем текст «Один» и условие проверки, чтобы получить «=Если(A1=1;"Один";Если(A1=2;"Два";1))»;11) опять вставляем на место последнего параметра («
1») содержимое буфера и корректируем формулу. И так повторяем пять раз, пока не получим результат: «=Если(A1=1; "Один";Если (A1=2;"Два";Если(A1=3;"Три";Если(A1=4; "Четыре";Если(A1=5;"Пять";)))))».Проанализируем, что у нас получилось. Всего я ввел текст из 26 символов: «
=1Один2Два3Три4Четыре5Пять». Сама формула заняла 91 символ. Вводя формулу Мастером и применив буфер обмена, я сократил работу на 71 %.
Пример 2. Разделяй и властвуй
На рис. 6 показан фрагмент подробного отчета о движении ТМЦ на складе в количественном выражении. В колонке «
Дата» записана дата операции. Затем идет наименование товара и объем его реализации. По этим данным нужно получить компактные сведения о динамике реализации ТМЦ за весь период. Такую задачу в Excel 2007 решить несложно — это займет полминуты. Если бы не ключевое слово «компактные»… Забегая наперед, скажу, что в Excel есть прекрасные инструменты обработки данных. Это и сводные таблицы, и подведение итогов, и консолидация. Но для их эффективного применения нужно вначале подготовить исходные данные. Например, если я построю сводный отчет по базе, как она есть (рис. 6), расположив по строкам даты, а по колонкам — наименования ТМЦ, то степень детализации такого отчета будет день. Представьте, что в таблице собраны данные за целый год и в ней фигурирует сотня товаров. Тогда сводный отчет получится размером в 365 строк и 100 столбцов. Проанализировать такую таблицу абсолютно нереально. Нужно ее сократить. Например, построить данные не по дням, а по месяцам. Чтобы это сделать, нужно в исходную таблицу добавить отдельную колонку. В эту колонку написать формулу, которая из полноценной даты выберет только номер месяца и номер года. Взяв такую колонку в качестве признака для группировки, можно мгновенно получить нужный результат стандартными средствами Excel. Мы еще неоднократно вернемся к этому приему в наших примерах, а пока задача такова. 
Добавить к таблице колонку «
МесГод» (рис. 7). В нее записать формулу, которая каждую дату представит в виде номера месяца и номера года, разделив их символом «-». Кроме того, если номер месяца состоит из одной цифры, его нужно дополнить нулем слева. Это обеспечит правильную сортировку данных. Иначе может оказаться, что при сортировке строк в алфавитном порядке октябрь («10-200х») будет предшествовать февралю («2-200х»). Иными словами, для дат с «01.01.200х» по «31.01.200х» формула должна вернуть значение « 01-200х», для февраля — « 02-200х», для декабря — « 12-200х». Мы составим формулу в ячейке «B2» и скопируем ее вниз на всю таблицу. Формула в «B2» должна быть такой: «=Если(ДлСтр(Месяц(A2))=1;"0"&Месяц(A2);Месяц(A2))&"-"&Год(A2)». 
В этой формуле функция «
Год(А2)» определяет номер года для даты, записанной в «А2», функция «Месяц(А2)» возвращает номер месяца этой даты. Функция «ДлСтр(Месяц(A2))» определяет количество символов (длину строки) в выражении «Месяц(A2)». Для января это значение будет «1», начиная с февраля оно станет равным «2». Функция «Если()» проверяет количество символов в месяце (выражение «ДлСтр(Месяц(A2))»). Если оно равно единице, функция сцепляет две строки: «0» и номер месяца, который получен функцией «Месяц(A2)», т. е. «"0"&Месяц(A2)». Иначе функция «Если()» просто возвращает значение «Месяц(A2)». Результат работы функции «Если()» с помощью операции сцепления («&») объединяется с разделителем «-» и функцией «Год()».Формула получается длинной. Будем решать задачу по действиям, как в начальной школе. Ячейку «
B2» пока оставляем пустой. В любые свободные ячейки таблицы (я выбрал «B3» — «B6») запишем функции, которые понадобятся для составления формулы.1. В ячейку «
В3» с помощью Мастера функций вводим формулу «Год(A2)», адрес «А2» выбираем на рабочем листе.2. В «
В4» с помощью Мастера строим формулу «Месяц(A2)». Заметьте, что адрес для функции мы указали тот же.3. В ячейке «
В5» Мастером функций формируем выражение «ДлСтр(1)». Параметр функции «1» фиктивный. Он не играет никакой роли и нужен только для того, чтобы завершить работу с Мастером и получить заготовку функции. Позже мы ее откорректируем.4. В ячейке «
В6» строим функцию «Если(1;1;0)». Параметры функции «Если()» пока тоже фиктивные, они нужны только для того, чтобы использовать шаблон функции, а не набирать ее вручную.К этому моменту у нас есть все составляющие будущей формулы. Остается собрать ее в одно целое в ячейке «
B2» и откорректировать параметры. Здесь уместно еще раз напомнить, что формула в Excel — это обычный текст. Поэтому к ней применимы все операции работы с текстом, в том числе работа с буфером обмена. Очень важно понять разницу между копированием в буфер фрагмента электронной таблицы и текста формулы.Важно!
Когда вы находитесь на рабочем листе Excel, при копировании в буфер обмена попадает объект Excel — ячейка или блок ячеек. Вместе с содержимым ячейки в буфер будут скопированы и другие атрибуты: данные форматирования, параметры защиты, примечания. Войдя внутрь ячейки (дважды щелкнув на ней мышью или нажав клавишу «F2»), вы покидаете рабочий лист. Включается режим текстового редактора, и открывается для редактирования содержимое ячейки. В буфер обмена будут копироваться выделенные вами символы. При этом навигация по рабочему листу становится недоступной: курсор будет перемещаться только в пределах содержимого ячейки, т. е. в пределах текстовой строки.Используя клавишу «
Shift» в сочетании со стрелками (или выделяя блок с помощью мыши), можно отметить фрагмент текста и поместить его в буфер обмена обычным способом (через значок на панели инструментов, сочетание клавиш «Ctrl+C» или «Ctrl+Ins»). Таким образом, в буфере обмена будет храниться не объект Excel, а текст формулы (полностью или частично). Чтобы перенести его в другую формулу, нужно вначале войти в режим ее редактирования. Для этого мы возвращаемся на рабочий лист, нажав клавишу «Esc». Далее становимся на формулу, куда хотим вставить текст из буфера обмена, и нажимаем «F2» (входим в редактирование ячейки). Найдя место вставки текста, извлекаем данные из буфера обмена (клавишами «Ctrl+V», «Shift+Ins» или через значок на панели инструментов).Вернемся к примеру и приступим к построению формулы для ячейки «
В2»:1) становимся в ячейку «
В4», нажимаем «F2». Попадаем в режим редактирования ячейки. В ней записан текст «=Месяц(A2)». Выделяем строку «Месяц(A2)» (без символа «=») и копируем в буфер («Ctrl+Ins»);2) нажав клавишу «
Esc», возвращаемся на рабочий лист. Переходим внутрь ячейки «В5». Здесь записан текст «=ДЛСТР(1)». Удаляем символ «1», вместо него вставляем содержимое буфера обмена. Получим формулу «=ДлСтр(Месяц(A2))». Выделяем текст формулы без символа «=» и копируем его в буфер;3) нажимаем «
Esc» и переходим в «В6». Входим в режим редактирования, вместо первого параметра функции «Если(1;1;0)» вставляем содержимое буфера. Получаем «=Если(ДлСтр(Месяц(A2));1;0)». Завершаем условие, дописав текст «=1» после функции «Длстр()», чтобы получить формулу: «=Если(ДлСтр (Месяц(A2))=1;1;0)»;4) подходим к ячейке «
B4», запоминаем в буфере текст «Месяц(A2)». Переносим текст в формулу на место второго и третьего параметра. Получаем формулу: «=Если(Длстр(Месяц(A2))=1 ;Месяц(A2);Месяц(A2))»;5) осталось в функции «
Если()» исправить второй параметр, дописав операцию сцепления номера месяца и строки «0». Окончательно получим: «=Если(Длстр(Месяц(A2))=1; "0"&Месяц(A2);Месяц(A2))»;6) из ячейки «
B3» копируем содержимое «Год(A2)», вставляем его в формулу ячейки «B6» и объединим операцией сцепления так, чтобы получить выражение: «=Если(Длстр(Месяц(A2))=1;"0"&Месяц(A2);Месяц(A2))&"-"&Год(A2) ». В этом выражении текст «&"-"&» придется набрать вручную, а «Год(A2)» вставляем из буфера обмена.Формула для ячейки «
B2» готова. Остается выделить текст в ячейке «B6» (именно текст, используя режим редактирования) и вставить его в «B2». Далее действуем как обычно: выделяем блок ячеек, в которые нужно поместить формулы, и нажимаем «Ctrl+D». Блок будет заполнен формулами по образцу из ячейки «B2». Ссылки Excel откорректирует автоматически.Проанализируем, сколько информации нам пришлось напечатать с клавиатуры при создании формулы:
— создавая Мастером функцию «
Длстр()», мы ввели ее параметр — «1»;— создавая Мастером функцию «
Если()», мы ввели три фиктивных параметра: «1», «1» и «0»);— три раза напечатали знак операции сцепления «
&»;— ввели текст «
=1», заканчивая условие в функции «Если()»;— напечатали разделитель «
-» и строку «0».Нам пришлось напечатать всего 13 символов. Формула в конечном варианте содержит 61 символ.
Мы сократили работу на 75,4 %! Но главное не в экономии количества нажатий на клавиши, а в том, что, пользуясь Мастером функций и буфером обмена, мы минимизировали ошибки при записи функций и переносе из одной ячейки в другую. Думаю, работая над примером, вы самостоятельно разгадали еще один секрет.Секрет
Создание формулы по частям облегчает проверку правильности работы каждой ее составляющей. Как только очередное изменение приведет к ошибочному результату, сразу станет понятно, где кроется ошибка.
Пример 3. Завершая прайс-лист
В документе «Прайс лист» из предыдущей статьи* мы оставили без внимания две формулы. В объединенной ячейке «
F8» нужно написать комментарий относительно даты действия скидок для дилеров (рис. 8). Кроме того, в ячейке «A13» нужно вставить сообщение, которое предупредит о дате последней корректировки курсов валют. Опираясь на уже известные приемы, сделать это совсем несложно: 
* Н. Карпенко «Excel 2007: именованные диапазоны» // «Б & К», 2009, № 8.
1) становимся в любую свободную ячейку, например в «
I8»;2) входим в Мастер функций, выбираем категорию «
Полный алфавитный перечень»;3) набираем комбинацию «
СЕ», сразу попадаем на функцию «Сегодня()»;4) у этой функции нет параметров, нажимаем «
ОК». В ячейке «I8» появится текущая дата — это результат работы функции;5) входим в редактирование ячейки «
I8». Запоминаем текст «Сегодня()» в буфере обмена;6) переходим в ячейку «
I9». Мастером вставляем функцию «Текст()». Ее можно выбрать из общего списка или раздела «Текстовые». У функции два параметра. Первый — число, которое нужно обработать. В нашем случае здесь будет стоять текущая дата. Иными словами, функция «Сегодня()». Вставляем содержимое из буфера обмена прямо в поле первого параметра;7) заполняем второй параметр. Здесь находится строка форматирования. В разных версиях Excel она может отличаться. В моей редакции строка выглядела так: «
ДД/ММ/ГГ». Иногда нужно ввести «dd/mm/yy». Возможно, придется поэкспериментировать. Завершив ввод параметра, нажимаем «ОК». Убеждаемся, что формула работает;8) между знаком «
=» и началом формулы впечатываем поясняющий текст, чтобы окончательно получить «="Скидки для дилеров на день "& ТЕКСТ(СЕГОДНЯ();"ДД/ММ/ГГ")»;9) в режиме редактирования выделяем текст формулы и запоминаем в буфере обмена. Это важно, так как я собираюсь копировать формулу в другую ячейку и хочу, чтобы Excel не корректировал адреса. Если в буфер записать текст формулы (а не содержимое ячейки), корректировок не будет;
10) переходим к ячейке «
F8» и вставляем содержимое буфера обмена;11) остается разобраться с ячейкой «
A13». Формула в этой ячейке должна сравнить текущую дату (функция «Сегодня()» или содержимое ячейки «B1» на листе «Спр») с датой корректировки курсов валют. Если даты не равны, нужно вывести предупреждающее сообщение и дату корректировки курсов. Она есть на листе «Спр» в ячейке «B2». Для этой формулы нам понадобится вначале построить функцию «Если()» с фиктивными параметрами. В отдельной ячейке построить выражение для преобразования даты корректировки курсов в текстовое значение функцией «Текст()», а затем объединить результат, чтобы получилась такая формула: «=Если(Текст(Сегодня();"dd.mm.yyyy") <>Спр!B2;"Проверьте курсы валют. Последняя дата корректировки "&Текст(Спр!B2;"dd/mm/yyyy");"")». Думаю, опираясь на опыт предыдущего примера, вы сможете проделать это самостоятельно.
Отладка формул
Работая над формулой по частям, мы не только упрощаем создание сложных выражений за счет привлечения Мастера и работы с буфером обмена. Важнейшее преимущество данного метода — возможность контролировать каждый этап своей работы, своевременно локализовать ту часть будущей формулы, где возникла ошибка. Но это не единственный механизм контроля за построением формул. В Excel есть и другие средства для решения этой задачи. В частности, окно контрольных значений. Познакомиться с ним мы просто обязаны: чем разнообразнее будет ваш арсенал контроля за вычислениями, тем продуктивнее будет работа.
Переходим в главное меню, выбираем раздел «
Формулы» и находим группу иконок «Зависимости формул» (рис. 9). В ней четыре элемента, нам нужно «Окно контрольного значения». Щелкаем на этой иконке левой кнопкой мыши. Появится окно, изображенное на рис. 10. В окне всего две кнопки: «Добавить контрольное значение…» и «Удалить контрольное значение…». Вот как ими воспользоваться: 
1. Становимся на ячейку «
H2» листа «Прайс». В ней записана специальная цена товара «Блокнот».2. Щелкаем на «
Добавить контрольное значение…». В окне появится строка с названием книги «Прайс.xls», именем листа «Прайс», адресом ячейки «H2», далее можно видеть результат формулы «4,48» и текст формулы «=C2*(1-Спр!$B$9)» (четвертая строка на рис. 10). Содержимое окна обновляется при изменении данных, как и ячейки рабочего листа. В окне контрольных значений можно собрать данные о работе формул с разных листов рабочей книги. Это очень удобно, так как избавляет от необходимости перелистывать рабочую книгу, чтобы убедиться в правильности вычислений. Дополнительным преимуществом окна контрольных значений является тот факт, что в нем видны имена диапазонов, если они были назначены ячейкам рабочего листа. 
Важно!
И все же окно контрольных значений никоим образом не умаляет важности поэтапного построения формул. Это основной, универсальный прием. Попробуйте внести в окно контрольных значений ссылку на «F8» листа «Прайс». Все, что удастся увидеть, — результат работы всей формулы. Работу каждой функции в формуле Excel показать не может.Оставшиеся иконки в группе «Зависимости формул» позволяют отслеживать взаимосвязи между ячейками рабочего листа. Откройте документ прайс-лист. Щелкните на листе «Прайс» и сделайте текущей ячейку «H2». В ней записана формула «=C2*(1-Спр!$B$9)». Нажмите кнопку «Влияющие ячейки». На листе появятся стрелки, которые указывают на взаимосвязи между ячейками «C2» и «H2». Отдельная стрелка покажет взаимосвязь между «H2» и рабочим листом «Спр». Кнопку «Зависимые ячейки» нужно использовать, когда текущей является ячейка с данными и нужно выяснить, с какими формулами эти данные связаны. Просмотр взаимосвязанных и влияющих ячеек удобен при поверке логики связей в электронной таблице.
Формулы и примечания
Каждый раз, обновляя таблицу, Excel вынужден заново распознавать и пересчитывать каждую формулу. Когда таблица невелика, все работает мгновенно. Но как только таблица станет большой, обрастет сложными формулами, ее обработка может откровенно «подтормаживать».
Вернемся для примера к базе данных на рис. 7. В реальной жизни она насчитывала около 95000 строк. Соответственно только в колонке «
МесГод» формула «=Если(Длстр(Месяц(A2))=1;"0"&Месяц(A2); Месяц(A2))&"-"&Год(A2)» повторялась 95000 раз. И это только по одной колонке… Обычное обновление данных на рабочем листе занимало несколько секунд. Когда к таблице добавился десяток столбцов с промежуточными вычислениями, компьютер стал откровенно тормозить. В такой ситуации можно было применить простое решение — скопировать таблицу в буфер обмена, а затем через специальную вставку преобразовать ее в значения. Иными словами, «забыть» формулы и оставить только результат их работы. По сути задачи это было вполне допустимо: бессмысленно каждый раз пересчитывать все значения в колонке «МесГод» для всех строк исходной таблицы. Даты в базе мы не меняем. Колонка «МесГод» носит вспомогательный характер и служит только для группировки данных. Поэтому достаточно один раз обсчитать ее, запомнить полученные значения и затем пользоваться ими. И все же терять формулы почему-то не хотелось — вдруг еще понадобятся? Я применил прием, который советую взять на заметку.К любой ячейке Excel можно добавить
примечание. Для этого нужно нажать правую кнопку мыши, из контекстного меню выбрать пункт «Вставить примечание». Появится небольшое окошко, в котором можно напечатать текст. Ячейка с примечанием будет помечена красным треугольником в правом верхнем углу (рис. 11, ячейка «B2»). Стоит навести указатель мыши на этот треугольник, и Excel автоматически покажет примечание в виде всплывающей подсказки. 
Примечания можно корректировать. Для этого нужно щелкнуть правой кнопкой мыши на ячейке с примечанием и выбрать из контекстного меню пункт «
Изменить примечание». С этого момента можно работать с примечанием как с обычным текстом. Какое отношение имеют примечания к специальной вставке? Самое непосредственное.Секрет
В примечаниях можно хранить тексты формул. В любой момент такой текст из примечания можно скопировать в буфер обмена и вернуть на рабочий лист. Вот что это дает применительно к нашей базе на рис. 7. Мы можем пройтись по одной строке таблицы (в примере это строка «2») и последовательно перенести тексты формул из ячеек в примечания к ячейкам. Затем скопировать все данные в буфер обмена, вернуть их оттуда как значения, тем самым удалив формулы с рабочего листа. Но примечания к ячейкам при специальной вставке останутся! Таблица без формул будет обрабатываться мгновенно. В то же время в любой момент мы сможем войти внутрь примечания, взять оттуда текст формулы, вставить его на рабочий лист. Затем скопировать формулу по всей таблице и восстановить ее первоначальное состояние.Чтобы сохранить текст формулы внутри примечания, нужно сделать так:
1. Нажав «
F2», войти в режим редактирования ячейки. В примере это ячейка «B2».2. Скопировать текст формулы в буфер обмена.
3. Вернуться на рабочий лист, нажав «
Esc».4. Щелкнуть на ячейке правой кнопкой мыши, из контекстного меню выбрать «
Вставить примечание».5. В окне примечания вставить текст формулы из буфера обмена.
Теперь формулы можно удалить. Для этого делаем так:
1. Выделяем всю таблицу (можно нажать «
Ctrl+А»).2. Копируем ее в буфер обмена («
Ctrl+С»).3. Не снимая выделения, щелкаем на значке списка у иконки «
Вставить» меню «Главная». Раскроется перечень возможных вариантов. Выбираем «Специальная вставка…».4. В окне специальной вставки находим переключатель «
Вставить» и ставим его в положение «Значения».5. Нажимаем «
ОК».Все! Вместо формул в таблице остались значения. Только в одной ячейке в виде примечания хранится текст формулы. В любой момент можно войти в режим изменения примечания, скопировать текст формулы в буфер и вернуть его в ячейку рабочего листа. Для больших таблиц экономия места на жестком диске и прирост скорости обработки просто колоссальные.
Выводы
1. Формула в Excel — это текст, составленный по определенным правилам. Поэтому к формулам применимы все операции работы с текстом. При редактировании и создании формул можно использовать буфер обмена. К формулам можно применять операции контекстной замены.
2. Чтобы ускорить создание ссылок, не вводите адреса с клавиатуры. Адрес в формулу можно подставить щелчком мыши на рабочем листе.
3. Создавая формулу, контролируйте тип адресации. В большинстве случаев используют относительные ссылки, которые автоматически изменяются при копировании формул. Абсолютные ссылки при копировании формул не изменяются.
4. Чтобы изменить тип ссылки в формуле, нужно открыть ячейку на редактирование, установить курсор на адрес ссылки и последовательно нажимать клавишу «
F4». Каждое нажатие вставляет символ «$» перед различными составляющими адреса.5. Ячейкам таблицы можно присвоить имена. Использование имен упрощает чтение формул и способствует снижению количества ошибок. По умолчанию имена ячеек являются ссылками с абсолютной адресацией.
6. Excel 2007 содержит около 400 встроенных функций. Их использование облегчает решение многих практических задач. Вставить функцию удобно с помощью Мастера функций. При работе с Мастером адреса вводятся так же, как в формулах, — щелчком мыши на рабочем листе.
7. Мастер функций, буфер обмена, формирование ссылок по рабочему листу позволяют построить формулу любой сложности быстро и без ошибок.
8. Длинные формулы нужно строить по частям в отдельных ячейках, а затем объединить, используя буфер обмена.
9. Ячейки электронной таблицы могут содержать примечания. Обычно примечания служат для хранения «заметок на память». Но в примечаниях можно запомнить и тексты формул. Это позволяет ускорить обработку таблицы и в то же время оставить возможность восстановить формулы.
На этом мы завершаем тему работы с формулами и переходим к проблеме форматирования электронных таблиц в Excel 2007. И конечно же, нас будет интересовать главным образом бухгалтерская специфика в этом вопросе.
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
