Задание 2. Таблицы с двумя входами

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

Методические указания по выполнению задания 2:

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

1. Введите метки и данные, как показано на табл.4.1, или скопируйте данные на новый лист. Выделите произвольную ячейку базы данных (так что Excel сможет автоматически определить промежуток данных). В меню Данные выберите команду Сводная таблица. На первом шаге из трех Мастера сводных таблиц и диаграмм выберите пункты «В списке или базе данных Microsoft Excel» и «Сводная таблица». Нажмите Далее. На втором шаге Мастера убедитесь, что Excel правильно определил промежуток данных, и нажмите Далее.

2. На шаге 3 Мастера щелкните по кнопке Макет. В появившемся окне перетащите кнопку Владелец справа в область Строка слева. Щелкните по кнопке Оценка и перетащите ее в область Столбец в центре. Щелкните по полю ID справа и перетащите его в область Данные в центре. Когда вы отпустите кнопку мыши, кнопка ID может измениться на «Сумма по полю ID». Если название кнопки в области Данные отлично от «Количество по полю ID», то щелкните дважды по нему, в списке Операция окна Вычисление поля сводной таблицы выберите Количество и нажмите ОК. Результат данных действий изображен на рис. 4.8. Нажмите ОК.

Рис. 4.8. Макет таблицы с двумя входами.

3. На шаге 3 Мастера выберите пункт Существующий лист и введите Е1 или укажите ячейку верхнего левого угла таблицы с результатами. В примере при создании Сводной таблицы используются значения Excel по умолчанию.

Рис. 4.9. Таблица с двумя входами.

Для изменения установок щелкните по кнопке Параметры. Нажмите Готово. Результат показан на рис. 4.9.

4. Чтобы спрятать отсутствующие значения, щелкните по стрелке вниз поля в ячейке Е2. В ниспадающем списке уберите отметку со значка звездочки (*) и нажмите ОК. Новая таблица изображена на рис. 4.10.

Рис. 4.10. Таблица без отсутствующих значений.

5. Для облегчения сравнений перспектив бизнеса в зависимости от вида собственности количественные показатели следует выразить в процентах. Для выполнения данной задачи выделите произвольную ячейку с числом (любую в промежутке F3:L6, как показано на рис. 4.10). В контекстном меню выберите Параметры поля. Для отображения дополнительных параметров в окне Вычисление поля сводной таблицы щелкните по кнопке Дополнительно. В выпадающем списке Дополнительные вычисления выберите Доля от суммы по строке. Нажмите ОК.

6. Таблица будет лучше выглядеть, если убрать Общий итог. Щелкните по произвольной ячейке в таблице и в контекстном меню выберите Параметры таблицы. В окне Параметры сводной таблицы уберите отметку с пункта Общая сумма по столбцам. Нажмите ОК. Введите Доля по строкам в ячейку Е1 и установите требуемую ширину столбца. В результате таблица процентов будет выглядеть, как показано на рис. 4.11.

Рис. 4.11. Таблица долей по строкам.

7. Шесть категорий перспектив бизнеса могут быть избыточными. Для того чтобы оставить только две категории, воспользуйтесь командой Excel Группировать: выделите ячейки, содержащие заголовки 1, 2 и 3 (F2:H2). В контекстном меню выберите Группа и структура, а затем Группировать. Выделите ячейку с именем Группа 1, выберите последовательно в контекстном меню Группа и структура и Скрыть детали. Свернутая таблица показана на рис. 4.12.

Рис. 4.12. Частично свернутая таблица с двумя входами.

8. Выделите ячейки с заголовками 4, 5 и 6 (ячейки G3:I3 рис. 4.12) и примените команды Группирования, как описано на предыдущем шаге. Получится таблица, изображенная на рис. 4.13.

Рис. 4.13. Свернутая таблица с двумя входами.

9. Отформатированная версия данной таблицы представлена на рис. 4.14. Исходная сводная таблица осталась в ячейках Е1:Н6, так что она может быть изменена впоследствии с помощью команд Группирование и Параметры поля. Выделите Е1:Н6; щелкните Правка / Копировать; щелкните по ячейке J и выберите Правка / Специальная вставка / 3начения и форматы чисел. Произведите изменения, показанные на рис. 4.14. (Например, введите текст Оценка в ячейку К2; выделите K2:L2, выполните команду Формат / Ячейки / Выравнивание / По горизонтали / По центру выделения; нажмите ОК.)

Рис. 4.14. Отформатированная таблица с двумя входами.

Постройте Линейчатую диаграмму с отображением строчных процентов, как показано на рис. 4.15.

Рис. 4.15 Линейчатая диаграмма оценки бизнеса.