Реализация товаров в стоимостном выражении

Фирма	Продукция	Месяц	Стоимость
Аксай-нан	хлеб	январь
Аксай-нан	батон	январь
Аксай-нан	батон	февраль
Алюр-авто	ВАЗ-21009	январь
Алюр-авто	ВАЗ-2111	январь
Алюр-авто	ВАЗ-21009	март
Фудмастер	йогурт	февраль
Фудмастер	йогурт	март
Фудмастер	йогурт	апрель

Выполнение.

Скопируйте в буфер обмена таблицу в редакторе Word.

В Excel вставьте таблицу и произведите форматирование.

Задание 7.

При помощи команды Данные/Итоги подведите промежуточные итоги в стоимостном выражении:

· по фирмам;

· по месяцам среди всех фирм;

· по продукции среди всех фирм.

Задание 8.

Постройте диаграмму (одну), показывающую изменение стоимости реализации товаров по месяцам для каждой фирмы.

Задания для самостоятельной работы

Задание 1С.

Используя построенный телефонный справочник:

· отобразить записи, у которых номер дома начинается с «1»;

· отобразить записи, у которых номер дома равен «13»;

· отобразить записи, у которых номер квартиры равен 13.

· отобразить записи, у которых номер дома и номер квартиры равен «13»;

· отобразить записи, у которых номер дома и номер квартиры равен «13» или «17».

Задание 2С.

Создайте список служащих фирмы:

· отобразите список сотрудников, у которых не введена дата рождения;

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

· заполните пустые даты произвольными значениями;

· дополните список полями «ФИО», «ВОЗРАСТ», и «СТАЖ» и запишите формулы, рассчитывающие соответствующие значения;

· отобразите список сотрудников, с «высшим» образованием;

· на Листе 2 получите список сотрудников с не «высшим» образованием;

· отобразите 5% служащих, больше всего отработавших на фирме;

· отобразите три фамилии самых молодых служащих;

· отобразите список сотрудников, родившихся в 1971 году;

· отобразите список сотрудников, родившихся в мае месяце;

· отобразите список сотрудников, у которых фамилия начинается с символа «А»;

· отобразите список сотрудников, у которых фамилия и имя начинаются с символа «Г»;

· отобразите список сотрудников, у которых фамилия, имя и отчество начинаются с символа «В»;

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

· получите список специальностей, служащих этой фирмы;

· получите список значений поля ОБРАЗОВАНИЕ. Отсортируйте список в соответствии с образованием, начиная с «высшее»;

· получите список должностей, для этой фирмы. Отсортируйте список в соответствии с занимаемой должностью;

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

· постройте диаграмму, показывающую количественное распределение фирмы по образованию;

Лабораторная работа № 3.7 КОНСОЛИДАЦИЯ РАБОЧИХ ТАБЛИЦ

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

Задание 1.

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

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

Выполнение.

Для выполнения данного задания необходимо:

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

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

Задание параметров для диалогового окна «Консолидация»

В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

В поле «Ссылка» введите ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Если нужная книга закрыта, щелкните по кнопке «Обзор», чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов.

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

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

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

Построить требуемую диаграмму.

Задание 2.

Отредактировать исходные данные первого задания так, чтобы диапазоны консолидации стали идентичными. Провести консолидацию этих данных:

используя формулы, содержащие внешние ссылки. Для задания внешней ссылки используется формат:

=[Имя_рабочей_книги]Имя_листа!Адрес_ячейки

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

=’[Бюджет на 2001 год]Лист1’!A1

Если рабочая книга закрыта и не находится в текущей папке, то в ссылке необходимо указать полный путь к этой рабочей книге с помощью команд Вставить / Специальная вставка. Этот метод применим, если все используемые рабочие таблицы открыты. Недостатком этого метода является то, что консолидация получается нединамической (статическая консолидация). Скопируйте данные из первого диапазона исходной рабочей таблицы в буфер обмена. Активизируйте зависимую рабочую книгу и выберите ячейку, в которую нужно поместить консолидированные данные. Выполните команду Вставить / Специальная вставка, отметьте переключатель сложить и щелкните по кнопке ОК. Выполните эти действия для всех диапазонов рабочих таблиц, которые должны быть консолидированы с помощью команд Данные / Консолидация.

Лабораторная работа № 3.8 СВОДНЫЕ ТАБЛИЦЫ

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

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

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

Создайте базу данных, по которой будет составлен отчет (рис. 1).

Рис. 1.

А теперь создадим структуру отчета.

1. Скопируйте столбец Товар в пустую область рабочего листа.

2. Воспользуйтесь командой Данные-Удалить дубликаты (Data-Remove Duplicates), чтобы удалить дубликаты значений в ячейках.

3. Удалите заголовок Товар.

4. Скопируйте уникальный список товаров и выполните команду Специальная вставка-Транспонировать (Paste Special-Transpose), чтобы повернуть список на 90°.

5. Удалите вертикальный список товаров.

6. Скопируйте столбец Регион в пустую область рабочего листа.

7. Воспользуйтесь командой Данные-Удалить дубликаты (Data-Remove Duplicates), чтобы удалить дубликаты значений в ячейках.

8. Удалите заголовок Регион.

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

С помощью функции СУММЕСЛИМН (SUMIFS) просуммируем доход в ячейках, находящихся на пересечении строки товара и столбца региона.

На основе этой функции создается формула =СУММЕСЛИМН($G$2:$G$20;$C$2:$C$20;L$1;$B$2:$B$20;$K2), выполняющая вышеописанные действия (рис. 2).

Рис. 2.

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

В строку и столбец итогов введите заголовок Итого.

Сделайте подсчет итогов.

В результате выполнения всех этих действий должен получиться суммарный отчет (рис. 3.

Рис. 3.

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

· Можно ли отобразить названия товаров слева, а названия регионов — вверху?

· Можно ли создать отчет для клиентов из производственного сектора?

· Можно ли показать прибыль, а не общую выручку?

· Можно ли скопировать отчет для каждого из заказчиков?

Изобретение сводной таблицы

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

1. Щелкните на одной из ячеек построенной базы.

2. Щелкните на вкладке ленты Вставка (Insert).

3. Щелкните на кнопке Рекомендуемые сводные таблицы (Recommended PivotTables). Первая рекомендуемая таблица называется Доход по строке Регион (рис. 4).

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

3. Щелкните на кнопке OK, чтобы выбрать первую сводную таблицу.

4. Перетащите поле Товар из списка полей сводной таблицы в область КОЛОННЫ (COLUMNS), как показано на рис. 5.

Рис. 5. Чтобы завершить создание отчета, перетащите название поля Товар в область КОЛОННЫ

5. Отмените установку флажка Заголовки полей (Field Headers), находящегося в правой части ленты.

Рис. 6.

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

- Можно ли поменять местами столбцы и строки (товары и регионы)? Да, перетащите заголовок поля Товар в область СТРОКИ (Rows), а заголовок поля Регион — в область КОЛОННЫ (Columns).

- Можно ли создать тот же отчет для заказчиков из производственного сектора? Да, выполните команду Вставка-Срез (Insert-Slicer), выберите Сектор, щелкните на кнопке OK, а затем щелкните на пункте Производство.

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

- Можно ли создать копию отчета для каждого заказчика? Да, переместите поле Заказчик в область ФИЛЬТРЫ (Report Filter), раскройте список кнопки Параметры (Options), выберите параметр Показать страницы фильтров (Show Report Filter Pages) и щелкните на кнопке OK.

Дата добавления: 2014-10-31