Советы по применению формул Excel

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

Предположим, например, у вас есть таблица данных, начинающаяся с ячейки А1 и заканчивающаяся Н1000. Вы хотите применить одну или несколько формул поиска Excel для выбора необходимой информации. Так как ваша таблица может со временем увеличиться (по мере добавления новых данных), в формуле вы ссылаетесь на всю таблицу, включая все строки. Другими словами, эта ссылка может выглядеть приблизительно как А:Н или, возможно, А1:Н65536. Такой вариант используется для автоматического учета в формулах новых добавленных в таблицу данных.

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

Еще одна распространенная проблема, связанная с плохо спроектированными электронными таблицами, — это болезненно низкая скорость пересчета. Многие полагают, что эту проблему может решить режим вычисления вручную (Manual) - Сервис > Параметры > Вычисления (Tools > Options > Calculations).

Однако в общем случае это очень плохой совет, грозящий потенциальными бедами. Электронная таблица существует за счет формул и вычислений и доставляемых ими результатов. Если вы работаете с электронной таблицей в ручном режиме вычисления, рано или поздно в таблице появится необновленная информация. Формулы будут отражать старые, а не обновленные значения, так как в ручном режиме вычисления необходимо запускать пересчет, нажимая клавишу F9. Но об этом так легко забыть! Попробуем провести аналогию. Если бы тормоза на вашей машине были неисправны и, создавая излишнее трение, замедляли бы машину, то отключили бы вы педаль тормоза, положившись исключительно на ручной тормоз, вместо того чтобы устранить неисправность? Большинству из нас такая мысль даже не пришла бы в голову, однако множество пользователей, ни минуты не сомневаясь, переключают свои электронные таблицы в ручной режим вычисления. Если ситуация требует, чтобы таблица работала в ручном режиме вычисления, — у вас проблема с дизайном. Отнеситесь к этому серьезно, не решайте ее поверхностным или временным способом.

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

Множество альтернатив формулам массивов можно найти среди функций баз данных Excel. Несколько очень хороших примеров использования этих формул на больших таблицах данных и возврата результатов на основе нескольких критериев можно найти в справке Excel. Часто пользователи упускают еще одну возможность — сводную таблицу, которую мы рассмотрим в главе 4. Хотя, впервые встретив сводные таблицы, вы можете быть обескуражены, мы рекомендуем ближе познакомиться с этой замечательной возможностью Excel. Овладев искусством работы со сводными таблицами, вы будете удивляться, как раньше обходились без них.

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

Top