15 полезных советов пользователям Excel

Используя эти малоизвестные функции, вы сможете значительно повысить полезность электронных таблиц.

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

Прежде всего мы хотели бы дать предварительные рекомендации. Во-первых, некоторые из рассматриваемых функций доступны лишь после того, как активизирован дополнительный модуль Analysis ToolPak. Для этого в меню Tools следует выбрать пункт Add-ins, выставить флажок Analysis ToolPak и щелкнуть на кнопке OK. Если модуль Analysis ToolPak не установлен, система предложит установить его. И во-вторых, поскольку цель настоящей статьи состоит в том, чтобы дать вам лишь общее представление о рассматриваемых функциях, мы не имеем возможности подробно рассказывать об их применении. Чтобы получить более детальную информацию о любой из этих функций, введите имя соответствующей функции в окне справки Excel.

1 Функция COUNTIF показывает, сколько раз выполняется то или иное условие. Допустим, если в колонке A (ячейки A1:A31) указаны дни месяца, а в ячейках колонки B — объемы продаж за эти дни, то, чтобы узнать, сколько раз дневная выручка превышала сумму 5 тыс. долл., можно применить следующую функцию: =COUNTIF(B1:B31,">5000"). Подобным образом используется функция SUMIF, но результат ее выполнения представляет собой сумму значений, а не число случаев выполнения заданного условия.

2 Исходные данные для функции CHOOSE — числа от 1 до 29 и список (с числом элементов до 29), а результат — элемент списка, соответствующий заданному числу. С ее помощью можно, к примеру, определить, на какой день недели приходится та или иная дата. В этом случае рассматриваемую функцию следует использовать в сочетании с функцией WEEKDAY, которая указывает день недели (выраженный цифрой от 1 до 7) для заданной даты. После этого функция CHOOSE преобразует цифру в название дня недели. Допустим, дата указана в ячейке B2. Чтобы выяснить, на какой день недели она приходится, воспользуйтесь следующей формулой:

=CHOOSE(WEEKDAY(B2), "Вс", "Пн", "Вт", "Ср", "Чт", "Пт", "Сб")

3 Функция MOD (или modulus) определяет остаток от деления одного числа на другое. Так, результат вычисления =MOD(4,3) равен 1. Эту функцию можно применять для форматирования каждой второй строки в электронной таблице. Для этого нужно выделить подлежащие форматированию ячейки и выбрать пункт Conditional Formatting в меню Format. Затем следует выбрать Formula Is и ввести формулу =MOD(ROW(),2). (Значение ROW представляет собой номер текущей строки.) Щелкните на кнопке Format и установите параметры форматирования каждой второй строки на закладке Patterns. После этого щелкните на OK.

4 Функция DATEDIF (не рассматриваемая в документации большинства версий пакета Excel) вычисляет промежуток времени между двумя датами, который по желанию пользователя может быть выражен в полных годах, полных месяцах или днях. С помощью этой функции удобно вычислять возраст человека. Чтобы получить возраст в годах, в ячейке A1 укажите дату рождения человека, а в ячейку B1 введите следующую формулу: =DATEDIF(A1,NOW(),"y").

5 Функция NETWORKDAYS вычисляет число рабочих дней (исключая выходные) между двумя датами. Можно указать праздничные дни, которые не должны учитываться при подсчете. Введите даты праздников в колонке A и выделите их, затем в меню Insert выберите пункты Name и Define и присвойте диапазону ячеек имя hodidays. В ячейках B1 и C1 укажите начальную и конечную даты. Число рабочих дней в период между ними вычисляется по формуле

=NETWORKDAYS(B1,C1,holidays)

6 Функция CONVERT служит для преобразования единиц измерения. Так, если в ячейке A2 некоторое значение задано в дюймах и его нужно выразить в сантиметрах, воспользуйтесь следующей формулой: =CONVERT(A2,"in","cm"). Справочно-консультационная система Excel содержит полный список вариантов преобразований и аргументов для каждой функции.

7 Функция ISERROR выдает результат True, когда ячейка, на которую она ссылается, содержит ошибку, и результат False, когда ошибки нет. Объединив рассматриваемую функцию с функциямиNOT и IF, можно сформировать строку которая добавляет диапазон чисел, игнорируя ячейки с ошибками. Итак, если числа содержатся в диапазоне ячеек A2:A6, введите в ячейку формулу =SUM(IF(NOT(ISERROR(A2:A6)),A2:A6,"")), нажав комбинацию клавиш CTRL, SHIFT, ENTER. Это необходимо потому, что вводимая функция принадлежит к категории функций обработки массива (т. е. выполняет несколько вычислений с использованием нескольких значений).

8 Функция LARGE выдает n-ный элемент списка величин, расположенных по убыванию. Например, если в ячейках A2:A10 содержатся выраженные в баллах результаты тестов, то результат участника, занявшего третье место, можно получить, воспользовавшись формулой =LARGE(A2:A10,3). Аналогичная функция SMALL выдает n-ный элемент списка величин, расположенных по возрастанию.

9 С помощью функции SUBTOTAL можно вычислять промежуточные суммы, что может быть полезно при работе с отфильтрованными списками. В случае применения фильтров функция SUM не дает желаемого результата, поскольку суммирует как скрытые, так и видимые значения. А функция SUBTOTAL суммирует лишь видимые значения. Кроме того, пользователю не нужно самому вводить функцию SUBTOTAL; достаточно щелкнуть на кнопке AUTOSUM, расположенной на панели инструментов, и программа сгенерирует корректную функцию SUBTOTAL.

10 Для вычисления квадратного корня в пакете Excel предусмотрена функция SQRT; например, по формуле =SQRT(25) вычисляется квадратный корень из числа 25. Если же требуется извлечь, скажем, кубический корень из числа, следует воспользоваться математическим понятием, согласно которому извлечение кубического корня равнозначно возведению числа в степень 1/3. Поэтому вычисление кубического корня из 27 выполняется по формуле =27^(1/3). Этот принцип универсален: он позволяет находить корень любой степени через возведение числа в соответствующую дробную степень.

11 Поисковые функции позволяют отыскивать данные в таблицах. Предположим, в колонке A содержится список названий офисов, а в колонках B и C — показатели их коммерческой деятельности. Тогда функция =VLOOKUP ("Seattle",A2:C15,2, FALSE) отыщет в колонке A таблицы данных (A2: C15) слово «Seattle» и отобразит на экране соответствующее значение из колонки B (вторая колонка таблицы). Вводя в формулу значение FALSE, пользователь извещает Excel о том, что данные не отсортированы и необходимо искать слово, точно соответствующее указанному в формуле.

12 С помощью функции =TODAY() в ячейку вводится текущая дата. Ее можно использовать в макрокоманде для того, чтобы в качестве имени присвоить сохраняемому файлу текущую дату. Ниже приводится текст макрокоманды, сохраняющей файл с именем, заданным в ячейке A1. Введите в ячейку A1 формулу =Today() и проверьте ее, запустив следующую макрокоманду:

Sub savenamefromcell()
Dim savename AsString
savename=Sheets(1).Range("A1").Value & ".xls"
ActiveWorkbook.SaveAs Filname: =savename
End Sub

13 Функция FREQUENCY используется для подсчета числа экземпляров того или иного значения в некоторой последовательности величин. Для выполнения функции требуется набор диапазонов (или «карманов»), чтобы сгруппировать значения. Например, «карманы» 5, 10, 15 и 20 используются для того, чтобы выдать сообщение о частоте появления того или иного значения в диапазонах 0:5, 6:10, 11:15 и 16:20. FREQUENCY — это функция массива, поэтому прежде всего нужно выбрать диапазон ячеек такого же размера, как размер «кармана», а затем набрать функцию =FREQUENCY (A1:D15, F2:F5) и нажать комбинацию клавиш CTRL, SHIFT, ENTER. В данном примере для подсчета чисел в диапазоне A1:D15 используются «карманы» в диапазоне F2:F5.

14 Функция OFFSET упрощает создание динамических диапазонов. В публикуемом ниже примере эта функция, введенная в диалоговое окно Insert (Вставка) | Name (Имя), выдает список чисел в колонке A. При этом предполагается, что список начинается с содержимого ячейки A1 и что в данном диапазоне нет незаполненных ячеек: =OFFSET($A$1,0,0,COUNTA($A:$A),1). Если, например, присвоить данному диапазону ячеек имя FilledCells, функция =SUM(FilledCells) вычислит сумму значений, содержащихся в списке. По мере того как числа добавляются в список или исключаются из него, обновление результатов производится автоматически. Аргументами функции OFFSET служат: начальная или эталонная ячейка; число строк и колонок, расположенных выше, ниже или лежащих по обе стороны эталонной ячейки; число строк и колонок, значения которых необходимо определить.

15 Функция расчета будущей стоимости (future value function, FV) позволяет определять прибыль на инвестированный капитал. Чтобы рассчитать, какой будет через десять лет стоимость тысячи долларов, вложенных сегодня под 5% годовых (начисляемых ежемесячно как сложные проценты), используется формула =FV(5%/12,10*12,,-1000). Результат составит 1647,01 долл.

Если же делать ежемесячные взносы в размере 10 долл., то будущая стоимость инвестиции будет рассчитываться по формуле =FV(5%/12,10*12,-10,-1000) и составит 3199,83 долл. Отрицательные значения используются в формуле потому, что речь идет о выплате денег. Кстати, не забудьте о том, что значение процентной ставки нужно указывать с учетом периода, за который начисляется процент. В приведенном выше примере начисления производятся ежемесячно, поэтому процентная ставка составляет 5%/12.

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

Предлагаю ознакомиться с аналогичными статьями: