Структуризация данных

Создание структуры листа

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

Подготовка данных к структурированию

Структурируемые данные должны быть представлены в виде диапазона, в котором первая строка каждого столбца содержит подпись, остальные строки — однотипные данные; пустые строки или столбцы в диапазоне отсутствуют.

Перед началом создания структуры может потребоваться отсортировать данные, чтобы сгруппировать строки вместе. В показанном на рисунке примере диапазон был отсортирован по регионам, а затем по месяцам, после чего произошло объединение строк «Март» и «Апрель» восточного региона и строк каждого месяца западного региона.

Располагайте итоговые строки выше или ниже каждой группы детальных строк. Для получения наилучших результатов итоговые строки должны содержать формулы, ссылающиеся на ячейки во всех строках. В показанном на рисунке примере имеется строка под сведениями каждого месяца и региона. Например, данные по продажам в строках с 11 по 13 суммируются в строке 14. Также можно структурировать данные, в которых итоговые строки содержат описательный текст и иные данные.

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

Отображение и скрытие структурированных данных

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

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

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

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

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

Настройка структурирования со стилями

Автоматические стили можно применить непосредственно при структурировании или после него. Для структурированных строк Microsoft Excel использует такие стили, как «Уровеньстрока_1» и «Уровеньстрока_2». Для структурированных столбцов Microsoft Excel использует такие стили как «Уровеньстолб_1» и «Уровеньстолб_2». Стили используют полужирное, курсивное и другое форматирование текста, чтобы облегчить различение итоговых строк в данных. Можно изменять способы форматирования, определенные каждому из стилей, что позволяет применять различные стили оформления текста и ячейки для настройки внешнего вида структуры. Также можно использовать автоформата для форматирования структурированных данных.

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

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

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

Инструкции

1. В меню Данные выберите команду Группа и структура , а затем — Настройки.

2. Снимите флажок в строках под детальными, флажок в столбцах справа от детальных или оба эти флажка.

2. Если требуется задать стили структуры для автоматического применения к новой структуре, установите флажок Автоматические стили.

Инструкции

1. В меню Данные выберите команду Группа и структура , а затем — Настройки.

2. Установите флажок Автоматические стили.

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

3. Решите, как требуется структурировать лист — автоматически или вручную.

o Любой лист можно структурировать вручную.

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

o Если вы не знаете, какие на данном листе есть формулы или как они расположены, попробуйте структурировать лист автоматически, а если результат не будет соответствовать желаемому, отмените это действие и выполните структурирование вручную.

Упражнение

Выполните одно из следующих действий.

•  Структурируйте данные автоматически

1. Выделите диапазон ячеек, для которых необходимо создать структуру.

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

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

•  Структурируйте данные вручную

1. Выделите строки или столбцы, содержащие сведения.

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

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

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

3. Продолжайте выделение и группировку строк или столбцов сведений и выполнение команды «Группировать» до тех пор, пока не будут созданы все необходимые уровни структуры.

4. Если ранее не были применены автоматически, их можно применить в любой момент.

Инструкции

1. Выделите ячейки, к которым необходимо применить стили.

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

3. Установите флажок Автоматические стили.

4. Нажмите кнопку Применить стили.

Скрытие и отображение структурированных данных

Упражнение

1. Если на листе отсутствуют символы структуры ,  и , выберите в меню Сервис команду Параметры, откройте вкладку Вид и установите флажок символы структуры.

2. Выполните одно или несколько следующих действий.

•  Покажите или скройте подробные данные группы

1. Чтобы показать подробные данные группы, нажмите соответствующую этой группе кнопку .

2. Чтобы скрыть подробные данные группы, нажмите соответствующую этой группе кнопку .

•  Разверните или сверните структуру до определенного уровня

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

Например, если в структуре четыре уровня, скрыть четвертый уровень (оставив все остальные развернутыми) можно, нажав кнопку .

• Покажите или скройте все структурированные данные

1. Чтобы показать все подробные данные, нажмите среди символов структуры  кнопку самого низкого уровня. Например, если существует три уровня, нажмите кнопку .

2. Чтобы скрыть все подробные данные, нажмите кнопку .

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

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

1. Выберите лист.

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

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

Примечания

· Выделенные элементы структуры также можно разгруппировать без удаления всей структуры. Удерживайте нажатой SHIFT, щелкая кнопку  или  группы, затем в меню Данные укажите на пункт Группа и структура и выберите команду Разгруппировать.

· Чтобы скрыть структуру, не удаляя ее, покажите все данные, нажав кнопку с наибольшим числом в области структуры , выберите в меню Сервис команду Параметры, откройте вкладку Вид и снимите флажок символы структуры.

Выполнение анализов «что - если» для данных на листах

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

Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если" . Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.

Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р.

Изменение значения ячейки для получения определенного значения в другой ячейке

1. Выберите команду Подбор параметра в меню Сервис.

2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу .

3. Введите искомый результат в поле Значение.

4. В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку.

5. Нажмите кнопку OK.

Анализ таблиц данных

Таблицы данных

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

Таблицы данных с одной переменной . Таблица данных с одной переменной используется, например, при необходимости просмотреть, как различные процентные ставки влияют на размер ежемесячных выплат по закладной. В следующем примере ячейка D2 содержит формулу вычисления платежа, =ППЛАТ(B3/12;B4-B5), которая ссылается на ячейку ввода B3.

Таблицы данных с двумя переменными. Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по закладной различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.

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

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

Упражнение

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

1. Выполните одно из следующих действий:

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

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

2. Выделите таблицу подстановки, включая столбец или строку, где содержится новая формула.

3. В меню Данные выберите команду Таблица .

4. Выполните одно из следующих действий:

o если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

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

Удаление таблицы подстановки данных

Удаление всей таблицы

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

2. Выберите пункт Очистить в меню Правка , а затем – команду Все.

Удаление рассчитанных значений из таблицы подстановки данных

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

1. Выделите в таблице данных все рассчитанные значения.

2. Выберите пункт Очистить в меню Правка , а затем – команду Содержимое.

Преобразование рассчитанных значений таблицы подстановки данных в константы

1. Выделите в таблице данных все рассчитанные значения.

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

3. Нажмите стрелку рядом с кнопкой Вставить и выберите вариант Значения.

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

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

Упражнение

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

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

2. Выполните одно из следующих действий.

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

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

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

4. В меню Данные выберите команду Таблица .

5. Выполните одно из следующих действий:

o если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

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

Совет

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

Создание таблицы подстановки с двумя переменными

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

1. В ячейку листа введите формулу, которая ссылается на две ячейки ввода.

В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12;B4;-B5) следует ввести в ячейку C2.

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

В приведенном ниже примере различные процентные ставки следует ввести в ячейки C3, C4 и C5.

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

В приведенном ниже примере срок погашения ссуды (в месяцах) следует ввести в ячейки D2 и E2.

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

В приведенном ниже примере следует выделить диапазон C2:E5.

5. В меню Данные выберите команду Таблица подстановки.

6. В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке.

В приведенном ниже примере в поле Подставлять значения по столбцам в следует ввести B4.

7. В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце.

В приведенном ниже примере в поле Подставлять значения по строкам в следует ввести B3.

8. Нажмите кнопку OK.

Пример

Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по ссуде различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.

Ускорение пересчета листа, содержащего таблицу подстановки данных

1. В меню Сервис выберите команду Параметры, а затем — вкладку Вычисления.

2. Выберите вариант Автоматически кроме таблиц.

Примечание. При выборе этого варианта вычисления при пересчете книги таблицы не пересчитываются. Чтобы выполнить пересчет таблиц вручную, выделите формулу и нажмите клавишу F9.