Материал: 5442

41

Структурирование рабочих листов

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

Структурируемые данные должны быть представлены в виде списка с итоговыми данными по срокам и (или) столбцам.

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

Рисунок 26 – Вид структурированной таблицы

Структура может иметь до 8 уровней детализации, в которых каждый уровень обеспечивает подробную информацию для предыдущего уровня. В показанном на рисунке 26 примере строка «Итого по ДВ региону», содержащая итог для всех строк, имеет уровень 1. Строки, содержащие итоги для каждого края, имеют уровень 2, а конкретные данные по городам имеют уровень 3. Для отоб-

42

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

Рисунок 27 – Структурированная таблица со скрытыми деталями

Microsoft Excel позволяет структурировать данные двумя способами:

Автоматическое создание структуры

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

Microsoft Excel автоматически структурирует лист, отображая ровно столько подробной информации, сколько возможно:

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

-для структурирования листа целиком укажите любую ячейку;

-выберите в меню Данные команду Группа и структура, а затем – Создание структуры.

43

Создание структуры вручную

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

1.Выделите строки или столбцы, содержащие сведения. Строки или столбцы сведений обычно прилегают к строке или столбцу, содержащему итоговые формулы или заголовки. Например, если строка 9 содержит итоговые данные для строк с 5 по 8, выделите строки 5 – 8.

2.В меню Данные укажите на пункт Группа и структура, а затем выберите команду Группировать.

3.Рядом с группой на экране появятся знаки структуры.

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

Удаление структуры

1.Выделите данные.

2.Выполните команду Группа и структура в меню Данные, а затем –

Удалить структуру.

При удалении структуры никакие данные не удаляются.

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

Анализ данных

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

В Excel имеются следующие средства для анализа данных: Подбор пара-

метра, Таблицы подстановки и Поиск решения.

44

Подбор параметра

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

Например, в приведённом на рисунке 28 примере, для определения срока вклада в ячейке B4 в сторону увеличения до тех пор, пока сумма выплат в ячейке B6 не станет равна 1 000 000 р. можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис.

Рисунок 28 – Решение задачи методом подбора параметра

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

Таблица подстановки

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

том Exel Таблица подстановки.

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

45

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

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

Таблицы подстановки с одной переменной

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

В следующем примере рассмотрим технологию создания таблицы подстановки.

1.На свободном месте создайте список значений подстановки для переменной одной или нескольких формул, это можно сделать в отдельном столбце или строке. В нашем случае значения подстановки – различные сроки вклада, представленные в диапазоне D2:D14 (рисунок 29). Значения подстановки будут поочередно копироваться в ячейку ввода для вычисления.

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

3.Выделите диапазон, содержащий значения подстановки и формулы.

4.В меню Данные выберите команду Таблица подстановки, чтобы вывести на экран диалоговое окно Таблица подстановки.

5.Поскольку наши значения подстановки расположены в столбце, поместите курсор в поле Подставлять значения по строкам в: и укажите ячейку ввода (В4). Если значения подстановки расположены в строке, укажите соответствующую ячейку ввода в поле Подставлять значения по столб-

цам в:.