Равнение на середину!

Равнение на середину!

Каждый, кто столкнулся с электронными таблицами Microsoft Excel, как впрочем и с любыми другими, например Lotus 1-2-3, проходит три достаточно четко разграниченные стадии. Первая начинается с поверхностного знакомства и сопровождается массой вопросов самого примитивного характера: как заполнить ячейку, как ее отредактировать или отформатировать, как распечатать результат и почему он не лезет в один лист бумаги. Потом наступает вторая стадия. Пользовательский интерфейс худо-бедно освоен, и пользователя тянет попробовать все режимы меню и даже попробовать применить не слишком сложные формулы. Это приносит и пользу, и удовольствие.

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

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

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

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

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

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

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

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

Естественно, формулы суммирования следует строить в аналогичном порядке . В ячейке С7 располагаем формулу "=СУММ (С4:С6)". Аналогичные формулы, только с поправкой на имя столбца, должны быть введены в ячейки D7, E7, G7, H7, I7, C11, D11, E11, G11, H11 и I11. Конечно, в одиннадцатой строке суммируются ячейки из строк: восемь, девять и десять. Похожим образом должно быть организовано и построчное суммирование. Сумма продаж по каждой неделе складывается из ежедневных итогов по каждому конкретному наименованию товара. Например, в ячейке F4 суммируются значения из C4, D4 и Е4.

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

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

С итоговым суточным оборотом поступаем по аналогии. То есть сумма ячейки С12 (итоговый оборот всего предприятия за первый рабочий день) складывается из суточного оборота первого менеджера (С7) и второго менеджера (С11).

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

У меня получилась полностью подготовленная таблица, которую не грех распечатать в качестве какого-нибудь приложения к толстенному финансовому отчету. Ее же можно и по-другому использовать. Причем мне кажется, что в электронном виде она станет гораздо удобнее. Все, что от вас требуется, это провести операцию структурирования. Ее успех всецело зависит от того, насколько правильно составлена исходная таблица.

Процедура структурирования достаточно проста. Только ее проводить следует строго сверху вниз. То есть сначала создается самый верхний уровень, потом те, что в него вложены, и так до самого основания всей пирамиды связей.

Итак, приступим. Выделите все строки таблицы (если вы, конечно, повторяете на своем компьютере то, что я тут описываю) с третьей по одиннадцатую включительно. Потом следует обратиться к режиму "ГРУППА И СТРУКТУРА" из меню "ДАННЫЕ". Там расположена команда " СГРУППИРОВАТЬ" . После ее выполнения в левой части экрана рабочая область несколько отодвинется, и на служебном пространстве появится вертикальная черта с маленькой экранной кнопкой, помеченной символом "минус" . Эта черта обозначает соответствующий уровень иерархии, а ее протяженность показывает диапазон строк, которые считаются подчиненными для того места, против которого расположена кнопка с "минусом".

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

Аналогичным образом создаются и последующие слои вложения. К примеру, выделим строки с четвертой по шестую и вновь воспользуемся командой " СГРУППИРОВАТЬ". Мастер создаст элемент следующего уровня с такой же вертикальной чертой и управляющей кнопкой.

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

Закончив со строками, стоит повторить все эти процедуры со столбцами. Сначала выделяем диапазон первого, самого верхнего, уровня, соответствующий всему месяцу в целом (столбцы с B по J включительно) и производим группировку. Потом повторяем то же самое поочередно с диапазонами C-E и G-I, соответственно.

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

В качестве эксперимента попробуйте поиграть с изменением иерархии, что приведет к изменению на экране внешнего вида таблицы, и каждый раз заглядывайте в "ПРЕДВАРИТЕЛЬНЫЙ ПРОСМОТР", расположенный в меню "ФАЙЛ". Microsoft Excel тут же покажет то, как будет выглядеть результат печати, когда он выползет из принтера.

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

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

Можно сразу выделить всю таблицу целиком (естественно, только те ячейки, которые несут какую-либо смысловую информацию) и, вместо " СГРУППИРОВАТЬ", в том же месте следует воспользоваться командой " СОЗДАНИЕ СТРУКТУРЫ". Microsoft Excel сам проанализирует все связи, существующие в выделенной области, и одним махом соорудит все существующие уровни. Если же программа лопухнется и напортачит - не беда. В том же месте, где расположены команды группировки, есть и команды обратного действия.

"УДАЛИТЬ СТРУКТУРУ", соответственно, удаляет все элементы группировки в выделенном фрагменте. При этом уничтожается вся структура. И вертикальная, и горизонтальная. Это своего рода стрельба по площадям, когда нужно быстро привести файл в исходное состояние. Боги ведь тоже иногда ошибаются. Если же требуется более тонкая работа, например, отменить ошибочную группировку выбранного фрагмента (как отдельно строк, так и отдельно столбцов), то воспользуйтесь командой "РАЗГРУППИРОВАТЬ".

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

Александр Запольскис
E-mail: leshy@nestor.minsk.by - титульная страница