Часть3. Структура документа – группировка данных

Рассмотрим вопрос придания таблице Excel некоторой структуры. Воссоздайте в Excel складской список, показанный на рис. 16. Суммы в колонке E рассчитываются по формулам, а в ячейке E2 записана функция =СЕГОДНЯ().

Как видно, списки товаров перечислены по группам. При большом количестве товаров, списки не будут умещаться в один экран, не будут обозримы и для просмотра придётся пользоваться полосами прокрутки. Для удобства просмотра в Excel можно задавать группировку строк (по строкам или по столбцам).

 

 

Рис. 16

 

На рис. 17 показан наш список, сгруппированный по строкам. Кнопки – и + скрывают и раскрывают группы, а кнопки 1 и 2 скрывают/раскрывают уровни групп одновременно. На рис. 17 группа Телевизоры раскрыта, другие группы закрыты.

 

Рис. 17

 

Каждая группа создается отдельно. Для получения группировки, как на рис. 18, проделайте действия:

 

1. Выделите строки первой группы:

 

 

2. Выберите и выполните команду меню Данные à Группа и структура à Группировать. Это приведет к появлению раскрытой группы с кнопкой –.

3. Проделайте пункты 1 и 2 с другими группами.

4. Проверьте работу кнопок – и +.

5. Сохраните таблицу в файл (понадобиться при выполнении следующего задания).

Перед/после группировки строк желательно выполнить настройку следующим образом: вызвать окно настройки Д анные à Г руппа и структура à Н астройка…, далее в окне Настройка снять флажок "итоги в столбцах справа от данных" и нажать кнопку ОК или Применить стили. Это означает, что кнопки + и – будут располагаться над выбранной группой, а не под ней.

 

Перед разгруппированием также следует предварительно выделить строки группы. Для разгруппирования всех групп одновременно следует выполнить команду меню Данные à Группа и структура à Удалить структуру.

 

 

Далее создадим прайс-лист на основе сохраненного списка склада. Будем придерживаться определенных требований, а именно:

1. Прайс-лист располагается на соседнем (со списком склада) листе и содержит те же группы и тот же перечень товаров. Причем вся информация должна переноситься (браться) из складского списка.

2. Прайс-лист должен содержать колонки для оптовой и розничной цены. Оптовые цены вычисляются на основе закупочных цен из склада с учетом наценки1: Оптовая цена = Закупочная цена * Наценка1. Розничные цены вычисляются в рублях на основе оптовых цен с учетом курса и наценки2: Розничная цена/руб. = Оптовая цена * Наценка2 * Курс.

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

 

В итоге должна получиться таблица, как на рис. 18. Лист со складским списком называется СКЛАД, а лист с прайс-листом – ПРАЙС. Для подготовки такого прайс-листа в первую очередь следует вспомнить правила обращения к ячейкам другого листа. На рис. 18 показан пример такой ссылки - в ячейке D2 записана формула =СКЛАД!E2. Таким образом, в ячейку D2 листа ПРАЙС попадает значение даты из ячейки E2 листа СКЛАД. Не забудьте установить формат отображения (Дата) ячейки D2.

Лист со складским списком показан на рис. 16. Основные действия для получения прайса (рис. 18) следующие:

 

1. Откройте файл, сохранённый в предыдущем разделе. Переименуйте лист со складским списком в СКЛАД, а предполагаемый лист с прайсом в ПРАЙС, как обозначено на рис. 18.

2. В ячейку B1 запишите формулу =СКЛАД!В1, а в С2 указанный на рисунке текст.

3. В ячейку D2 запишите формулу =СКЛАД!E2 и установите формат отображения Дата.

4. Введите в ячейки A4-D4 необходимый текст. Выделите группу ячеек A4-D4 и установите (обведите) границы, нажав кнопку Границы на Панели инструментов.

5. В ячейки Е1-Е3 введите и выровняйте указанный на рисунке текст. В ячейки F1-F3 введите конкретные числовые значения.

6. Для строки 4 установите выравнивание ячеек с переносом по словам, вызвав окно Формат ячеек.

7. В ячейки А6-А9, А11-А18, А20-А22 введите ссылки на аналогичные ячейки листа СКЛАД. Для этого сначала запишите в ячейку А6 формулу =СКЛАД!А6, а затем скопируйте (перетащите) её в ячейки А7-А22. Поскольку ссылка в формуле относительная, то все формулы при копировании будут скорректированы.

Рис. 18

 

8. Аналогично п.7 заполните ячейки В5-В22, т.е. сначала запишите формулу =СКЛАД!В5 в ячейку В5, а затем скопируйте (перетащите) её в ячейки В6-В22.

9. Последовательно сгруппируйте строки 6-9, 11-18, 20-22. Сделайте настройку. После этих действий осталось заполнить колонки оптовых и розничных цен.

10. Заполните колонку оптовых цен. Для этого в ячейку С6 введите формулу =(СКЛАД!C6*(100+F$2))/100. В ней закупочная цена из ячейки СКЛАД!C6 умножается на процент наценки1 из ячейки F2. Здесь в формуле указана абсолютная ссылка на строку ячейки (F$2) для того, чтобы при копировании формул номер строки не изменялся. Размножьте формулу в ячейки С7-С22 и проверьте правильность копирования формул – правильность относительных и абсолютных ссылок.

11. Заполните колонку розничных цен. Для этого в ячейку D6 введите формулу =(C6*(100+F$3)/100)*F$1. В ней оптовая цена из ячейки С6 умножается на процент наценки2 из ячейки F3 и на курс доллара F1. Здесь в формуле также указаны абсолютные ссылки на строки ячеек (F$3 и F$1) для того, чтобы при копировании формул номер строки не изменялся. Размножьте формулу в ячейки D7-D22 и проверьте правильность копирования формул.

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

 

Попробуйте изменить наименование или закупочную цену листе СКЛАД – данные на листе ПРАЙС будут тут же пересчитаны. Попробуйте изменить значения наценок или курса на листе ПРАЙС – оптовые и розничные цены будут пересчитаны.

 

Можно присвоить ячейкам F1, F2 и F3 абсолютные имена. Например, Курс, Наценка1 и Наценка2 соответственно. Тогда приведённые выше формулы будут выглядеть более осмысленно: (СКЛАД!C6*(100+Наценка1))/100 и (C6*(100+ Наценка2)/100)* Курс.