ОП.09 ИНформационные технологии в профессиональной деятельности.. Пояснительная записка данные методические указания составлены для проведения практических занятий в процессе изучения дисциплины Информационные технологии в профессиональной деятельности

• Название: Пояснительная записка данные методические указания составлены для проведения практических занятий в процессе изучения дисциплины Информационные технологии в профессиональной деятельности
• Дата: 17.06.2022
• Размер: 5.56 Mb.
• Имя файла: ОП.09 ИНформационные технологии в профессиональной деятельности..pdf
• Тип: Пояснительная записка; #598570
• страница: 4 из 10

Тема занятия: Применение стандартных функций, создание вычисляемых условий.
2. Цель занятия: приобретение практических навыков в обработке табличной информации с помощью простейших логических и статистических функций в среде табличного процессора Microsoft Excel; графического анализа данных.
3. Оборудование, приборы, аппаратура, материалы: ПК, табличный процессор MS
Excel.
4. Краткие теоретические сведения.
СТРУКТУРА ДЕНЕЖНОЙ МАССЫ
864,8
2484,7
747,2
790,7
577,9
487,5
244,7
652,6
0
1000
2000
3000
4000
5000
США (1990 г.)
млрд.долл.
Россия (2003 г.)
млрд.р.
Наличные деньги
Депозиты до востребования
Крупные срочные депозиты
Сберегательные, срочные и т.п. депозиты
Краткосрочные векселя казначейства и коммерческие векселя

Для расчета экономических показателей используют функции MS Excel, которые можно найти: Вставка / Функция, категория Финансовые.
Амортизация −процесс постепенного переноса стоимости средств произ-водства на производимый продукт.
Со временем производственные фонды подвергаются износу, поэтому часть прибыли регулярно в течение определенного срока перечисляется в амортизационный фонд,
предназначенный для полного восстановлениясредств производства.
Для вычисления амортизации предназначены финансовые функции:
АПЛ(нач_стоимость;ост_стоимость;время_эксплуатации) −возвра- щает величину амортизации актива
1)
за один период, рассчитанную линей-ным методом.
Линейный метод−систематическое списание равных по величине сумм втечение всего срока существования актива.
АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации;период) − возвращает величину амортизации актива за данный период, рассчитанную методом суммы (годовых) чисел.
Метод суммы чисел − систематическое списание некоторой суммы в тече-ние t лет с использованием множителя, который вычисляется как отношение числа лет до окончания периода к сумме 1 + 2 + ... + t.
ДДОБ(нач_стоимость;ост_стоимость;время_эксплуатации;период; коэффициент) − возвращает значение амортизации актива за данный период, используя метод уменьшения остатка.
Метод уменьшения остатка − стоимость актива списывается более быст-рыми темпами по сравнению с линейным методом амортизации. При этом величина отчисляемой суммы рассчитывается не от первоначальной стоимо-сти, а от остатка после списаний, проведенных в предыдущие годы.
ПУО(нач_стоимость;ост_стоимость;время_эксплуатации;нач_период;
кон_период;коэффициент;без_переключения) −возвращает величину амор- тизации актива для любого выбранного периода с использованием метода уменьшения остатка.
ФУО(нач_стоимость;ост_стоимость;время_эксплуатации;период; месяцы) − возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.
Метод фиксированного уменьшения остатка вычисляет амортизацию, используя фиксированную процентную ставку.
Аргументы функций вычисления амортизации:
нач_стоимость −затраты на приобретение актива;
ост_стоимость −стоимость в конце срока амортизации(остаточнаястоимость актива);
время_эксплуатации −срок амортизации(количество периодов);
период −период,для которого вычисляют амортизацию.Измеряется втех же единицах, что и время_эксплуатации;
нач_период −начальный период,для которого вычисляют амортизацию;
кон_период −конечный период,для которого вычисляют амортизацию; коэффициент − необязательный аргумент функций ДДОБ() и ПУО() − процентная ставка снижающегося остатка при вычислении амортизации ме-тодом уменьшения остатка.
Коэффициент по умолчанию равен 2 (метод двукратного уменьшения остатка); без_переключения − необязательный аргумент функции ПУО() − логиче-ское значение
(0 или 1), указывающее необходимость перехода на линейный метод расчета амортизации, если она превышает уменьшающийся остаток; месяцы − необязательный аргумент функции ФУО() − количество меся-цев в первом году срока амортизации. По умолчанию этот аргумент равен 12.
Задание.
1 Загрузите MS Excel: Start / All Programs / MS Excel (Пуск / Все про-граммы / MS
Excel).
2
Сохраните книгу с именем ПЗ12.

3
Переименуйте листы:
4
Перейдите на лист Амортизация и выполните.
Пример 1. Определить размер ежегодных,одинаковых по величине амор-тизационных отчислений для актива, начальная стоимость которого состав-ляет 20000 $, остаточная стоимость − 2500 $ , а время эксплуатации − 10 лет.
Решение
Для расчета равных по величине сумм амортизационных отчислений ис-пользуем функцию АПЛ() или АМР() (приложение А).
Создадим таблицу с исходными данными. Для ячеек с денежными сумма-ми установим
Формат / Ячейки…, вкладка Число, Числовые форматы − Денежный:
В ячейке D4 выполним расчет выплаты за один период:
=АПЛ($A$2;$B$2;$C$2)
В результате получим значение
Пример 2. Определить размеры ежегодных амортизационных отчисленийдля актива, начальная стоимость которого составляет 20000 $, остаточная стоимость − 2500 $ , а время эксплуатации − 10 лет.
Решение
Для расчета амортизационных отчислений используем: АСЧ() − метод суммы
(годовых) чисел; ДДОБ() − метод уменьшения остатка;
ФУО() − метод фиксированного уменьшения остатка.
Создадим таблицу с исходными данными.
В ячейке E5 рассчитаем сумму амортизации за первый год:
=АСЧ($A$5;$B$5;$C$5;D5)
Выполним копирование формулы на диапазон ячеек E5:E14, в результа-те получим значения амортизационных выплат за каждый из десяти лет всего срока амортизации.
Самостоятельно:
Выполнить расчет амортизационных отчислений по даннымпримера 2, используя:
ДДОБ() − метод уменьшения остатка;
ФУО() − метод фиксированного уменьшения остатка.
Результаты представлены на рисунке 1.
6. Содержание отчета
Отчет должен содержать:
1.
Название работы.
2.
Цель работы.
3.
Задание и его решение.
4.
Вывод по работе.

Практическое занятие №9.
1.
Тема занятия: Решение линейных задач, дополнительные возможности Excel.
2.
Цель занятия: знакомство возможностями табличного процессора MS Excel по решению задач оптимизации и линейного программирования.
3.
Оборудование, приборы, аппаратура, материалы: ПК, табличный процессор
MS Exsel.
4.
Краткие теоретические сведения.
Глобализация экономики и ведение хозяйства в открытой конкурентной борьбе требуют от менеджеров и бизнесменов значительного повышения ответственности за качество принятия решений. Это основная причина, которая обусловливает необходимость применения научного подхода к проблеме выбора правильного управленческого решения из имеющихся альтернатив. Одним из направлений развития научнотехнического прогресса в данной сфере стал раздел математики, который именуется какматематическое
программирование. Это направление науки тесно связанное с практическими проблемами оптимального распределения ресурсов в различных отраслях производства и сферы услуг.
Различные аспекты оптимизации занимают важное место в бизнесе и деятельности современных организаций и предприятий. Поэтому данный курс может помочь на практике тем студентам и слушателям, которые сталкиваются с задачами оптимального управления в своей повседневной роботе (менеджеры, экономисты, финансисты, банковские работники), а также тем, кто заинтересован в расширении своего профессионального кругозора (например, с целью повышения конкурентного преимущества).
Проблемы оптимизации присутствуют в самых различных процессах производства: поставка сырья; оптимальный выпуск продукции; оптимальное управление запасами; оптимальное распределение ресурсов; планирование инвестиций; оптимальный рацион (смесь, сплав); назначение на должность (на работу); оптимальная замена оборудования и т.д
Решение задачи оптимизации состоит в поиске оптимального плана размещения ресурсов с использованием математических моделей и вычислительных методов, которые реализуются с помощью компьютеров и специальныхпрограмм-оптимизаторов.В данном курсе в представленных задачах и упражнениях все расчѐты будут сделаны с использованием оптимизационной программыSolver (Поиск решений), встроенной в популярный табличный процессор (электронные таблицы) MSExcel.
1.
Задание.
ЗАДАЧА 1. Фирма производит два вида А и В упаковочных ящиков.
Производство ящиков ограничивается со стороны наличия сырья и временными
параметрами машинной обработки. Для ящика типа А необходимо 3 м
2
досок, а для типа
В – 4 м
2
. Возможности фирмы-поставщика сырья ограничиваются 1700 м
2
досок в
неделю. Для производства ящика типа А необходимо 0,2 часа машинного времени, а для
типа В – 0,5 часа. Производственные мощности предоставляют 160 ч машинного
времени в неделю. Определите оптимальное соотношение в производстве изделий
каждого типа, если известно, что каждый ящик типа А приносит 2 доллара прибыли, а
типа В – 4 доллара прибыли.
Перед тем, как приступить к решению этой задачи в MS Excel, необходимо построить математическую модель.
Введем следующие обозначения:
х – количество ящиков типа А, выпускаемых за неделю,
у – количество ящиков типа В, выпускаемых за неделю.
Тогда прибыль от этих изделий равна Р(х,у)=2*х+4*у долларов. Таким образом, мы получили функцию прибыли (целевую функцию), и наша задача – ее максимизировать. Видно, что бесконечно возрастать функции не позволят ограничения, наложенные на аргументы: a.
Ограничение со стороны сырья: З*х + 4*у ≤ 1700; b.
Ограничение машинного времени: 0.2*х + 0.5*у ≤ 160.
Кроме этого, х и у не могут принимать отрицательные значения.

Если свести все полученные условия, то приходим к такой системе

















0 0
1700 4
3 160 5
,
0 2
,
0
max
4 2
)
,
(
y
x
y
x
y
x
y
x
y
x
P
Далее необходимо перенести построенную нами модель на лист табличного процессора.
Запустите приложение MS Excel или создайте новую рабочую книгу.
Присвойте одному из листов имя Задача 1 и создайте на нем следующую заготовку:
1.
Для удобства адресации и повышения понятности формул присвойте ячейкам, содержащим количество производимых ящиков модели А и В имена Х и Y соответственно.
2.
В ячейки с ограничениями и целевой функцией введите соответствующие формулы.
3.
После построения модели решения задачи необходимо обратиться к инструменту Поиск решения. Охарактеризуйте этот инструмент. Для чего он применяется?
4.
Установите курсор на ячейку с целевой функцией, после чего вызовите диалоговое окно Поиск решения. Обратите внимание в поле Установить целевую ячейку уже находится адрес ячейки с целевой функцией.
5.
Далее необходимо указать, что должно являться результатом оптимизации целевой функции. В нашем случае функция должна быть максимизирована, поэтому выберите соответствующую радиокнопку.
6.
Следующим шагом необходимо указать, какие ячейки должны изменяться при оптимизации. Установите курсор в поле Изменяя ячейки и щелкните по кнопке
Предположить. После этого в поле должны быть внесены необходимые нам адреса ячеек с количеством производимой продукции.
7.
Следующим шагом будет ввод ограничений, необходимых для правильной оптимизации целевой функции. В соответствующем блоке щелкните по кнопке Добавить и в появившемся диалоговом окне Добавление ограничения укажите ячейку с контрольным значением требуемых материалов и необходимое ограничение. Аналогично добавьте ограничения, связанные с затрачиваемым машинным временем. Не забудьте добавить ограничения на значения самих переменных х и у (они не отрицательные).
8.
После добавления ограничений вы увидите их в соответствующем списке.
Заметим, что ограничения могут свободно добавляться и удаляться из списка.
9.
Вызовите окно параметров оптимизации, щелкнув по одноименной кнопке. В окне Параметры поиска решения установите флажок у параметра Линейная модель. При помощи справочной системы ответьте, почему была выбрана именно эта модель оптимизации, и каково назначение прочих параметров этого диалогового окна.
10.
После ввода всех параметров оптимизации необходимо щелкнуть по кнопке
Выполнить. Далее, после поиска решения, на экран будет выведено диалоговое окно

Результаты поиска решения, в котором кроме сообщения «Решение найдено. Все ограничения и условия оптимально выполнены» предлагается на выбор два варианта действия: можно оставить найденное решение либо восстановить прежние значения ячеек.
Также это диалоговое окно поможет получить отчеты о самом процессе оптимизации.
11.
Нажмите ОК и найденные значения будут зафиксированы у вас на листе.
12. Сохраните документ под именем Оптимизация.xls.
13. Далее самостоятельно решите следующие задачи, записывая их решения на отдельных листах рабочей книги. В тетради приведите математические модели задач и результаты оптимизации.
Задача 2. Фирме требуется уголь с содержанием фосфора не более 0,03% и с примесью пепла не более 3,25%. Доступны три сорта угля А, В, и С. по следующим ценам (за тонну):
Сорт угля
Фосфор (%)
Пепел (%)
Це
на
А
0,06 2,0 30
В
0,04 4,0 30
С
0,02 3,0 45
Как их следует смешать, чтобы удовлетворить ограничениям на применение и минимизировать цену?
Выполнить самостоятельную работу и сохранить в свою папку.
2.
Отчет должен содержать:
1.
Название работы.
2.
Цель работы.
3.
Задание и его решение.
4.
Вывод по работе.
7. Контрольные вопросы
1. Для чего применяется надстройка Поиск решения?
2. Какие возможности для анализа хозяйственной деятельности предприятия даёт поиск решения?
Практическое занятие №10.
1. Тема занятия: Обработка данных: сортировка, фильтрация, построение сводных таблиц.
2.
Цель занятия: приобретение практических навыков в обработке табличной информации с помощью простейших логических и статистических функций в среде табличного процессора Microsoft Excel; графического анализа данных.
3.
Оборудование, приборы, аппаратура, материалы: ПК, табличный процессор
MS Excel.
4.
Краткие теоретические сведения.
1. Сортировка данных

Иногда таблицы могут содержать довольно большое количество данных, причем эти данные зачастую будут представлены в виде списка. В таком случае, очень помогают в работе такие инструменты, как сортировка списков и их фильтрация.
После сортировки записи отображаются в порядке, определенном значениями столбцов
(по алфавиту, по возрастанию/убыванию цены и пр.).
Можно выполнять сортировку данных по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым) в нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам или по формату, включая цвет ячеек, цвет шрифта, а также по значкам. Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам.
Критерии сортировки сохраняются вместе с книгой, предоставляя возможность повторного применения сортировки каждый раз при открытии книги в таблице Excel, но не к диапазону ячеек.
Сортировка текстовых значений
Выберите столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедитесь, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные.
На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр
Выполните одно из следующих действий:
Для сортировки алфавитно-цифровых символов по возрастанию выберите вариант
Сортировка от А до Я.
Для сортировки алфавитно-цифровых символов по убыванию выберите вариант
Сортировка от Я до А.
При необходимости можно воспользоваться сортировкой с учетом регистра.
Сортировка чисел
Выберите столбец с цифровыми данными в диапазоне ячеек или убедитесь, что активная ячейка находится в столбце таблицы, который содержит цифровые данные. На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр, а затем выполните одно из следующих действий:
Для сортировки чисел по возрастанию выберите вариант Сортировка от минимального к максимальному.
Для сортировки чисел по убыванию выберите вариант Сортировка от максимального к минимальному.
Сортировка значений даты и времени
Выберите столбец с датами и временем в диапазоне ячеек или убедитесь, что активная ячейка находится в столбце таблицы, который содержит значения даты и времени. Выберите столбец со значениями даты и времени в диапазоне ячеек или таблице. На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр, а затем выполните одно из следующих действий:
Для сортировки от самого раннего значения даты или времени к самому позднему выберите вариант Сортировка от старых к новым.
Для сортировки от самого позднего значения даты или времени к самому раннему выберите вариант Сортировка от новых к старым.
Сортировка по цвету ячейки, цвету шрифта или значку
Выберите столбец с данными в диапазоне ячеек или убедитесь, что активная ячейка находится в столбце таблицы. На вкладке Главная в группе Редактирование выберите команду
Сортировка и фильтрация, а затем выберите в списке пункт Специальная сортировка.
Отобразится диалоговое окно Сортировка. В группе Столбец в поле Сортировать по выберите столбец для сортировки. В группе Сортировка выберите тип сортировки. Выполните одно из следующих действий:
Для сортировки по цвету ячейки выберите пункт Заливка ячейки, а затем выберите цвет.
Для сортировки по цвету шрифта выберите пункт Цвет шрифта, а затем выберите цвет.
Для сортировки по набору значков выберите пункт Значок ячейки.

В группе Порядок выберите стрелку рядом с кнопкой, и затем, в зависимости от вида формата, выберите цвет шрифта, цвет заливки ячейки или значок ячейки. В группе Порядок выберите порядок, в котором необходимо осуществить сортировку. Выполните одно из следующих действий:
Для перемещения наверх или влево цвета ячейки, цвета шрифта или значка выберите вариант Сверху для сортировки по столбцу и Влево для сортировки по строке.
Для перемещения вниз или вправо цвета ячейки, цвета шрифта или значка выберите вариант Снизу для сортировки по столбцу и Вправо для сортировки по строке.
Сортировка по нескольким столбцам или строкам
Сортировку можно осуществлять по нескольким столбцам или строкам для группировки данных с одинаковыми значениями в одном столбце или строке и последующего осуществления сортировки другого столбца или строки в этих группах с одинаковыми значениями. Можно одновременно осуществлять сортировку по 64 столбцам.
2. Фильтрация данных
Основное отличие фильтра от упорядочивания - это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.
Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.
Для применения автофильтра нажмите ту же кнопку, что и при сортировке -
«Сортировка и фильтр» и выберите пункт «Фильтр» (конечно же, перед этим должен быть выделен диапазон ячеек).
В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра.
Поля, по которым установлен фильтр, отображаются со значком воронки. Если подвести указатель мыши к такой воронке, то будет показано условие фильтрации.
Для формирования более сложных условий отбора предназначен пункт «Текстовые фильтры» или «Числовые фильтры». В окне «Пользовательский автофильтр» необходимо настроить окончательные условия фильтрации.
При использовании расширенного фильтра критерии отбора задаются на рабочем листе.
Для этого надо сделать следующее.
Скопируйте и вставьте на свободное место шапку списка. В соответствующем поле
(полях) задайте критерии фильтрации.
Выделите основной список.
Нажмите кнопку «Фильтр» на панели «Сортировка и фильтр» ленты «Данные». На той же панели нажмите кнопку «Дополнительно». В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек. В результате отфильтрованные данные появятся в новом списке.
1.
Задание.
Откройте файл Сортировка и выполните следующие задания:
1.
Выбрать данные из таблицы по шоколаду Люкс и Сливочный и упорядочить их по названию шоколада и по его стоимости по возрастанию. Результат отбора и таблицу критериев сохранить на этом листе.
2.
Выбрать заказы шоколада Мечта и упорядочить в порядке убывания количества заказов по каждому менеджеру отдельно. Результат отбора и таблицу критериев сохранить на этом листе.
3.
Выбрать шоколад производителей корона и Колизей, цена которого выше 1000 руб. и расположить данные по убыванию количества. Результат отбора и таблицу критериев сохранить на этом листе.
4.
Выбрать данные о менеджерах, заказы которых больше 50, и упорядочить данные по фамилии менеджера в алфавитном порядке и количеству заказов по убыванию.
Результат отбора и таблицу критериев сохранить на этом листе.
6. Содержание отчета
Отчет должен содержать:

1.
Название работы.
2.
Цель работы.
3.
Задание и его решение.
4.
Вывод по работе.
Практическое занятие №11.
1. Тема занятия: Обработка данных: структурирование таблиц, подведение итогов, консолидирование данных.
2. Цель занятия: изучить процесс консолидации данных, научиться объединять значеня из нескольких диапазонов данные с помощью консолидации.
3.
Оборудование, приборы, аппаратура, материалы: ПК, табличный процессор
MS Exsel.
4.
Краткие теоретические сведения.
Консолидация данных – это объединение значений из нескольких диапазонов данных, например, сведение в итоги значений, находящихся в диапазонах на разных рабочих листах.
Чтобы подвести итоги и составить отчет по результатам нескольких листов, можно консолидировать данные из отдельных листов в основном листе. Листы могут находиться в той же книге, что и основной лист, или в других книгах. При консолидации данных они компонуются так, что их становится проще обновлять и обобщать на регулярной основе или по требованию.
Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в корпоративный лист по расходам. Этот основной лист может содержать общие и средние объемы продаж, текущие количества товаров на складах и сведения о продуктах, пользующихся наибольшим спросом, по всей организации.
Чтобы консолидировать данные, воспользуйтесь кнопкой Консолидация в группе
Работа с данными на вкладке Данные.
Если требуется
Действие
Упорядочить данные во всех листах, задав им одинаковый порядок и расположение
Консолидация по расположению
Организовать данные на разных листах по разным принципам, но с использованием одинаковых названий строк и столбцов, чтобы их можно было сравнить в основном листе
Консолидация по категории
Применять формулы со ссылками на ячейки или объемными ссылками на другие листы из-за отсутствия постоянных позиций или категорий, на которые можно было бы опираться
Консолидация по формуле
Использовать отчет сводной таблицы вместо консолидации
Использование отчета сводной таблицы для консолидации данных
Консолидация по расположению
1.
Организуйте консолидируемые данные на каждом отдельном листе + Настройка данных
2.
Щелкните на основном листе левый верхний угол области, в которой требуется разместить консолидированные данные. Примечание. Убедитесь, что справа и снизу этой ячейки достаточно свободных ячеек для данных консолидации. Команда Консолидация заполнит столько ячеек, сколько потребуется.
3.
На вкладке Данные в группе Средства обработки данных выберите команду
Консолидация.

4.
Выберите в раскрывающемся списке Функция итоговую функцию, которую требуется использовать для консолидации данных.
5.
Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор. Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.
6.
Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
7.
Выберите способ обновления консолидации. Выполните одно из следующих действий.
Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными. Важно. Этот флажок можно устанавливать только в случае, если лист с исходными данными находится в другой книге.
После того как этот флажок установлен, изменить набор входящих в консолидацию ячеек и диапазонов невозможно.
Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.
8.
Оставьте пустыми все поля в группе Использовать в качестве имен. Названия исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать названия в консолидированные данные, сделайте это вручную.
Консолидация по категории
1.
Организуйте консолидируемые данные на каждом отдельном листе + Настройка данных
2.
Щелкните левый верхний угол области основного листа, в которой требуется разместить консолидированные данные.
3.
На вкладке Данные в группе Средства обработки данных выберите команду
Консолидация.
4.
Выберите в раскрывающемся списке Функция итоговую функцию, которую требуется использовать для консолидации данных.
5.
Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор. Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.
6.
Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
7.
Выберите способ обновления консолидации. Выполните одно из следующих действий.
Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными.
Важно. Этот флажок можно устанавливать только в случае, если лист с исходными данными находится в другой книге. После того как этот флажок установлен, изменить набор входящих в консолидацию ячеек и диапазонов невозможно.
Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.
8.
Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: либо подписи верхней строки, либо Значения левого столбца, либо оба флажка одновременно.
Примечания
Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.

Все категории, которые не нужно консолидировать, должны иметь уникальные подписи, которые встречаются только в одном диапазоне исходных данных.
Консолидация по формуле
1.
На основном листе введите (вручную или путем копирования) названия строк и столбцов, содержащих консолидируемые данные.
2.
Щелкните ячейку, в которую следует поместить данные консолидации.
3.
Введите формулу, включающую ссылки на исходные ячейки каждого листа или трехмерные ссылки на данные, для которых будет выполняться консолидация. Для ссылок на ячейки выполните одно из следующих действий.
Если данные для консолидации находятся в разных ячейках разных листов
Введите формулу со ссылками на ячейки других листов, по одной на каждый лист.
Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:
Совет. Чтобы ввести ссылку на ячейку (например, Продажи!B4), не используя клавиатуру, введите формулу до того места, где требуется вставить ссылку, щелкните ярлычок листа, а затем — нужную ячейку.
Если данные для консолидации находятся в одинаковых ячейках разных листов
Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов.
Например, для консолидации данных в ячейках A2 всех листов от «Продажи» до «Маркетинг» включительно, в ячейку A2 основного листа следует ввести:
Примечание. Если формулы в книге вычисляются автоматически, консолидация с помощью формул также будет автоматически обновляться при изменении данных на отдельных листах.
Использование отчета сводной таблицы для консолидации данных
Из нескольких диапазонов консолидации можно создать отчет сводной таблицы.
Данный метод схож с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий. Дополнительные сведения см. в разделе Объединение нескольких листов в одном отчете сводной таблицы.
4.
Задание.
Задание 1
1.
Откройте файл сортировка
2.
Перейдите к листу Лист 1.
3.
Отсортируйте таблицу по данным столбца Марка телефона( по алфавиту)
4.
Перейдите к листу Лист 2.
5.
Выполните сложную сортировку таблицы: сначала по данным столбца Точка продажи, а затем по данным столбца Сумма выручки (в порядке убывания).
6.
Перейдите к листу Лист 3.
7.
Отсортируйте только данные столбца Дата (от новых к старым).
8.
Перейдите к листу Лист 4 9.
С использованием автофильтра отобразите только строки, относящиеся к магазину номер 3 10.
Перейдите к листу Лист 5 11.
С использованием автофильтра отобразите только строки, относящиеся к магазину №2 и телефону Самсунг
12.
Перейдите к листу Лист 6 13.
С использованием автофильтра отобразите только строки, в которых цена больше 5000 рублей
14.
Перейдите к листу Лист 7 15.
С использованием автофильтра отобразите только строки, в которых в столбце
Марка телефона значения отмечены желтой заливкой
16.
Перейдите к листу Лист 8

17.
С использованием автофильтра отобразите только строки с датой поставки в прошедшем месяце.
18.
Перейдите к листу Лист 9 19.
С использованием автофильтра отобразите только строки с суммой выручки от
15000 до 20000 20.
Перейдите к листу Лист 10 21.
С использованием автофильтра отобразите только строки с десятью наибольшими значениями в столбце Цена
22.
Перейдите к листу Лист 11 23.
С использованием фильтра отобразите только строки, у которых значения в столбце Объем продаж такое же, как в ячейке F9 24.
Перейдите к листу Лист 12 25.
Откажитесь от отбора строк по столбцу Марка телефона
26.
Перейдите к листу Лист 13 27.
Снимите фильтры с таблицы.
Задание 2
1.
Открыть файл Consolidation1.xlsx и объединить таблицы расположенные на листах 2010 год, 2011 год, 2012 год в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям. Таблицу разместить на листе с именем «ИТОГ».
2.
Открыть файл Консолидация_самостоятельная.xlsx и выполнить задание.
3.
Выполнить объединение таблиц из разных файла (John.xls, Rita.xls и
Stiven.xls) . Результат разместить в файле с именем «Сводный отчет».
6. Содержание отчета
Отчет должен содержать:
5.
Название работы.
6.
Цель работы.
7.
Задание и его решение.
8.
Вывод по работе.
2.
Контрольные вопросы
1.
Для чего применяется консолидация данных?
2.
Какие виды консолидации существуют?
Практическое занятие №12.
1.