Пример 2. Фильтрация списков.
Основное отличие фильтра от упорядочивания - это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок. Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.
Для применения автофильтра нажмите ту же кнопку, что и при сортировке "Сортировка и фильтр" и выберите пункт "Фильтр" (перед этим должен быть выделен диапазон ячеек).
В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра.
Поля, по которым установлен фильтр, отображаются со значком воронки. Если подвести указатель мыши к такой воронке, то будет показано условие фильтрации.
Для формирования более сложных условий отбора предназначен пункт "Текстовые фильтры" или "Числовые фильтры". В окне "Пользовательский автофильтр" необходимо настроить окончательные условия фильтрации
Для формирования более сложных условий отбора предназначен пункт "Текстовые фильтры" или "Числовые фильтры". В окне "Пользовательский автофильтр" необходимо настроить окончательные условия фильтрации.
При использовании расширенного фильтра критерии отбора задаются на рабочем листе. Для этого надо сделать следующее:
1). скопируйте и вставьте на свободное место шапку списка;
2). в соответствующем поле (полях) задайте критерии фильтрации;
3). выделите основной список;
4). нажмите кнопку "Фильтр" на панели "Сортировка и фильтр" ленты "Данные";
5). На той же панели нажмите кнопку "Дополнительно".
В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек.
В результате отфильтрованные данные появятся в новом списке.
Задание 1.
1. Создать базу данных сотрудников фирмы.
· При создании списка сначала наберите заголовки заданных столбцов, добавьте новые столбцы «Оклад», «Премия», «Всего».
· Введите записи и задайте формулы для вычисляемых ячеек (столбцы Премияи Всего). Начислить каждому работнику премию в размере К% (положить сначала К=10%).
2. Осуществить поиск записей
· Найти сотрудников, фамилии которых заканчиваются на «ов».
· Найти в списке сотрудников, проживающих на улице Мира.
· Найти сотрудников, проживающих на улице Мира, в доме №165.
3. С помощью фильтрации:
· Извлечь список Фамилий, Имен, Отчеств и Телефонов сотрудников, у которых фамилия начинается на «И» и телефон содержит последние цифры «07».
· Извлечь список Ф.И.О. сотрудников, проживающих на улице Мира в доме №165.
· Извлечь список Фамилий и адресов сотрудников по имени Ирина с телефоном, содержащим начальные цифры 22 и последнюю 0.
· Извлечь все данные для сотрудников, фамилии которых начинаются на «Б», отчество Владимирович (вна), и проживающих по улице Мира, имеющих оклад от 5000 до 10000 руб.
· Извлечь список Ф.И.О., нетелефонизированных сотрудников.
4. Отсортировать данные: а) по алфавиту улиц; б) «по алфавиту фамилий» и «по убыванию № телефонов».
Задание 2.
Создать таблицу реализации печатной продукции с заголовками столбцов: Месяц, Название, Тип издания (газета, журнал и т.д.), Цена одного экземпляра, Кол-во проданных экземпляров, Сумма от реализации.
1). Ввести информацию для двух месяцев (например, для января, февраля) и трех типов изданий.
2). Используя команду автоформат, оформить таблицу в удобном для пользователя виде.
3). Отсортировать данные в алфавитном порядке по Типу издания и одновременно по Кол-ву проданных экземпляров в порядке возрастания.
4). Используя автофильтр, показать только те издания, у которых количество проданных экземпляров меньше 10 (т.е. не пользующихся спросом).
5). Используя расширенный фильтр, показать только те издания, у которых Цена экземпляра больше или равна 6 р. Результат скопировать в другой диапазон.
Задание 3. Используя следующую таблицу:
1. Отобрать из базы данных магазина товары, стоимость партии которых находится в пределах от 200 000 до 600 000 руб. включительно.
2. Отобрать из базы данных товары двух типов: Телевизор CS-21 K2 MJQ, Телевизор CS-21 M6 MQQ.
3. Отобрать из базы данных магазина товары, которые поступили на склад в количестве 30 штук, и стоимость партии которых была меньше 600 000 руб.
4. Отобрать товар: Телевизор CS-21 K2 MJQ, или товары, поступившие на склад магазина в количестве менее 20 штук.
5. Отобрать из базы данных магазина следующие товары, DVD-комбо SV-DVD 645, стоимость которых менее 8000 р., фотоаппарат S 70, стоимостью более 18 000 р. и Телевизор CS-21 K2 MJQ стоимостью менее 7000 р.
6. Отобрать из базы данных товар, стоимость партии которого меньше среднего значения по магазину.
7. Отобрать из базы данных сведения о телевизорах, поступивших на склад с 15.09 по 31.10.2010., стоимость партий поставки которых была больше среднего по магазину, а также о товаре, стоимость поставки которого была максимальной.
8. С помощью сортировки и фильтра найдите: а) три самых дорогих товара; б) на какой день поступила самая большая партия товара; в) какой товар чаще всего, а какой больше всего поступает на склад.
Задание 4.Используя следующую таблицу:
1) Прочитав справку в Excel, изучить формулу ПРОСМОТР и по цифровому коду валюты определить ее курс; буквенный код валюты; наименование валюты. 2) Используя формулу ПРОСМОТР по буквенному коду валюты определить ее курс; цифровой код валюты; наименование валюты.
3) Найти значения формул ВПР и ГПР и проверить по таблице их истинность: =ВПР(840;A3:E19;5;0); =ВПР(A15;A3:E19;1); =ВПР(840;A3:E19;2;0) =ГПР(A3;A3:E19;5;0); =ГПР(A3;A3:E19;5); =ГПР(974;A3:E19;5;0)
4) Изучить функцию ПОИСКПОЗ, найти значения следующих формул: =ПОИСКПОЗ(840;A3:A19;0); =ПОИСКПОЗ(840;A3:A19;1) =ПОИСКПОЗ(840;A3:A19;-1).
Задание 5.Используя функции просмотра, автоматизировать предоставление скидки и выдачу подарка клиентам, приобретающим товары на сумму более 1000 рублей (скидка 1%, подарок - блокнот); более 2000 руб.(2% скидки, подарок - одеколон); более 5000 руб. (скидка 5%, подарок - калькулятор); более 10000 руб.(7% скидки. подарок - билеты на концерт).
Задание 6. Исходя из размеров выручки, рассчитать премиюУказание: Сначала нужно определить процент премии, используя таблицу выплаты премии, с помощью функций ПРОСМОТР и ВПР.
Задание 7:с помощью функций ГПР и ПОИСКПОЗ проставить цену в соответствии с прейскурантом:
Задание 8.В рамках расчета единого социального налога нужно определить сумму налога в фонд социального страхования (ФСС РФ) по каждому сотруднику фирмы. Расчеты выполнить, используя данные, представленные в таблице:
Указание: На одном рабочем листе набрать список физических лиц, их совокупный доход за 2005 год и рассчитать сумму налога. Для расчета налога нужно представить на отдельном листе исходную таблицу в следующем виде:
Лабораторная работа 8. Анализ и обобщение данных в электронных таблицах Excel.
Цель работы:
1. Обобщение и анализ данных с использованием Сводных таблиц.
2. Выполнение консолидации данных.
3. Освоение операции автоматического подведения итогов.
Для того чтобы обобщить и проанализировать данные можно воспользоваться встроенным в MS Excel 2010 средством «Сводная таблица». Для создания Сводной таблицы на вкладке Вставкав группе Таблицынажмите кнопку Сводная таблица(Рис.8.1.)
Рисунок 8.1. - Создание сводной таблицы
Далее в появившемся окне в поле Выбрать таблицу или диапазонавтоматически выберется вся ваша таблица. Если нужно выбрать только часть таблицы для анализа, то нажмите кнопку в конце строки ввода и мышкой выделите нужную часть таблицы. Обратите особое внимание на то, что верхняя строка выделенного диапазона обязательно должна содержать названия столбцов, т.к. она не будет обрабатываться как данные, а определит будущие названия полей, по которым будет происходить формирования отчета Сводной таблицы. После выделения нажмите на кнопку в конце строки ввода еще раз. Далее нажмите Ok(Рис.8.2).
Рисунок 8.2. - Выбор диапазона данных для создания сводной таблицы
Теперь у вас откроется новый лист, в котором и будут формироваться отчеты Сводной таблицы. В левой части листа формируется сам отчет, а в правой список полей Сводной таблицы и параметры формирования отчета. В списке полей (Рис.8.3.) выберите те, по которым вам нужен отчет, а в нижней части мышкой поместите поля в нужные области в нужном порядке (в начале можно проиграться с полями, перетаскивая их в разные области и следя за изменением отчета в левой части экрана (Рис.8.4)).
Рисунок 8.3 - Список полей Сводной таблицы и параметры формирования отчета.
В данном примере сформируется отчет с суммой продаж по городам и по товарам в каждом городе (Рис.8.4.).
Рисунок 8.4. - Сводный отчет с суммой продаж по городам и по товарам в каждом городе.
Консолидация данных представляет собой полезный способ объединения данных из нескольких источников в одном отчете. Такой отчет может содержать итоговые и средние данные о продажах, текущих уровнях материальных запасов и наиболее популярных продуктах в рамках всей организации. Для консолидации данных имеется кнопка Консолидацияв группе Работа с даннымина вкладке Данные.
Чтобы свести и включить в отчет значения, находящиеся в диапазонах на разных рабочих листах, можно выполнить консолидацию данных из каждого отдельного рабочего листа в сводный отчет на главном рабочем листе. Для этого:
1. Организуйте консолидируемые данные на каждом отдельном листе:
· Необходимо, чтобы все диапазоны данных были представлены в формате списка (первая строка каждого столбца содержит название, остальные строки однотипные данные; пустые строки или столбцы в списке отсутствуют).
· Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.
· Убедитесь, что макеты всех диапазонов совпадают.
· Убедитесь, что подписи столбцов или строк, которые требуется объе-динить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таб-лице консолидации.
· Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулыв группе Определенные именанажмите кнопку Имя диапазонаи введите имя диапазона в поле Имя.
2. Щелкните на основном листе левый верхний угол области, в которой требуется разместить консолидированные данные.
3. На вкладке Данныев группе Средства обработки данныхвыберите команду Консолидация.
4. Выберите в раскрывающемся списке Функцияитоговую функцию, которую требуется использовать для консолидации данных.
5. Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
6. Выберите способ обновления консолидации:
· чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными;
· чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.
7. Установите флажки в группе Использовать в качестве имен, указывающие где в исходных диапазонах находятся названия: либо подписи верхней строки,либо значения левого столбца,либо оба флажка одновременно.
Задание 1. Консолидация данных.
1. Создайте таблицу по образцу на листе Исходный: Фирма «Юпитер»
2. Создайте два новых рабочих листа и присвойте им имена Итоги и Вычисления.
3. Скопируйте рабочий лист Исходныйна лист Итогии выполните вычисления в столбце Объем продаж.
4. На листе Итогитребуется вычислить суммарное количество программ, реализованных каждым продавцом, объем продаж для каждого продавца и подвести итоги в целом по фирме.
5. Определите эффективность работы каждого продавца, его вклад в общий объем продаж. Для этого в итоговую таблицу на листе Вычислениядобавьте заголовок нового столбца Н – Доля,% и введите формулу для расчета процентной доли каждого продавца в общем объеме продаж.
6. На основе таблицы с итоговыми данными постройте объемную столбчатую диаграмму, используя столбцы «Продавец» и «Доля,% ».
7. Создайте три новых рабочих листа и присвойте им имена Юпитер, Сатурни Консолидация.
1. Скопируйте рабочий лист Исходныйна лист Юпитери выполните вычисления в столбце Объем продаж.
2. Скопируйте данные рабочего листа Юпитерна лист Сатурн.Откорректируйте данные на рабочем листе Сатурн:
· измените название фирмы;
· так как на фирме "Сатурн" реализацией программных продуктов занимается только один человек, удалите в таблице строки, относящиеся к одному из продавцов, измените фамилию продавца и некоторые данные в столбце Количество.
3. Используя консолидацию, вычислить суммарный объем продаж и количество программ от различных фирм-производителей, реализованных всеми продавцами обеих фирм ("Юпитер" и "Сатурн"). Таблицу с итоговыми (консолидированными) данными разместите на рабочем листе Консолидация.
4.Откорректируйте в таблице с консолидированными данными ширину столбцов так, чтобы был виден текст всех заголовков столбцов.
5. Удалите столбец с меткой Цена,$.
6. Используя кнопку Автосумма,вычислите общий объем продаж и количество программ, реализованных на обеих фирмах –"Юпитер" и "Сатурн".
7. Проанализируйте полученные результаты.
8. Отформатируйте таблицу с консолидированными данными рамками и заливкой, выделите метки столбцов и строк полужирным шрифтом.
9. Постройте диаграмму на основе итоговой таблицы.
Задание 2. Сводные таблицы.
1. Наберите в табличном процессоре Excel исходный список (таблицу) объемов продаж фирмы «Гигант».
· Задайте первому рабочему листу имя «Исходная».
· При создании списка сначала наберите заголовки столбцов, затем задайте форматы ячеек: для столбцов Покупатель, Товар, Ед. изм., – общий, Количество – числовой целый, Цена за ед., Сумма, Сумма с НДС – денежный с двумя десятичными знаками, НДС, % – процентный, Дата сделки – Дата.
· Введите первую запись и задайте формулы для вычисляемых ячеек (столбцы Сумма и Сумма с НДС).
· Создайте форму и введите остальные записи таблицы. В поле НДСформы вводите значение 0,25, Excel сам переведет это значение в %.
2. Подсчитайте итоговые суммы по столбцам Сумма и Сумма с НДС.
3. Создайте заголовок таблицы Объемы продаж фирмы «Гигант», отформатируйте заголовок.
4. Скопируйте таблицу с заголовком и без итоговых цифр по столбцам Сумма и Сумма с НДС на второй рабочий лист. Выполните сортировку данных по возрастанию по столбцам Покупатель и Товар.
5. Подсчитайте промежуточные итоги и общий итог продаж по покупателям.
6. Задайте имя Покупателирабочему листу. Создайте заголовок таблицы «Данные по покупателям», отформатируйте заголовок.
7. Выполните фильтрацию данных по товару Конфеты и дате сделки с 02.11.07 по 08.11.07 .
8. На основе исходной таблицы создайте сводную таблицу вида:
9. Задайте рабочему листу имя Покупатели. Создайте заголовок таблицы Данные по покупателям, отформатируйте заголовок.
10. Составьте сводную таблицу для получения объемов продаж с 01.11.2007 по 02.11.07, 03.11.2007 по 04.11.07, с 05.11.2007 по 06.11.07, 06.11.2007 по 08.11.07.
В результате должна получиться следующая сводная таблица:
11. Проведите группировку данных по периодам.
Создайте заголовок таблицы Итоги по периодам, отформатируйте его.
12. Постройте сводную таблицу, в которой указан процент количества каждого типа проданного товара от общего числа.
Задание 3. Анализэффективности рекламной компании с помощью «Таблицы данных».
В рабочем листе вычисляется чистая прибыль после продажи рекламируемого товара как функция двух входных параметров: количества разосланных рекламных материалов и предполагаемого процента ответов.
1. Введите текст в ячейку А1 – «Анализ эффективности почтовой рекламы», в А3 – «Входные данные» и объедините ячейки А1:В1 и А3:В3.
2. Введите текст в ячейки в соответствии с таблицей
3. Задайте имена ячеек В4, В5, В7, В8, В9, В10, В11, В12, В13 в соответствии с текстом, находящимся в соседних ячейках столбца А (Ре- жим Формулы Создать из выделенного фрагмента).
4. Введите необходимые числовые значения и расчетные формулы:
a. Стоимость печатных материалов (изменяется в зависимости от количества): 0,30 руб. – если количество экземпляров не более 200000; 0,20 руб. – от 200001 до 300000 экземпляров; 0,10 руб. – если больше 300000.
b. Почтовые расходы – их стоимость фиксирована и составляет 1,2 руб. за одно почтовое отправление.
c. Число заказов, т.е. количество ответов, которые предполагается получить, определяется в зависимости от процента предполагаемых ответов и от количества разосланных материалов по формуле: = Процент ответов * Число заказов. (в формулах нужно использовать присвоенные ячейкам имена).
d. Доход на один заказ – число фиксированное, т.е. компании известно, что за каждый заказ она получит прибыль 55 рублей.
e. Суммарный доход вычисляется по формуле: = Доход на один заказ * Число заказов.
f. Суммарные расходы состоят из стоимости печатных материалов и почтовых услуг: = Разослано материалов * (Стоимость печатных материалов + почтовые расходы)
g. Чистая прибыль определяется как разница суммарных доходов и суммарных расходов.
5. В диапазон ячеек В15:Н15 введите значения для переменной Процент_ответов: от 2,00% до 3,25% с шагом 0,25%.
6. В диапазон ячеек А16:А25 введите значения для переменной Разослано_материалов от 100 000 до 325 000 с шагом 25 000.
7. В ячейку А15 введите формулу для вычисления чистой прибыли.
8. Создайте таблицу подстановки. Для этого выделите диапазон А15:Н25 и выполните команду Данные– Анализ «Что-Если»–таблица данных. В диалоговом окне в поле Подставлять значения по столбцамвведите имя Процент_ответов, а в поле Подставлять значения по строкам– Разослано_материалов.
9. По полученным данным постройте трехмерные гистограммы, представляющие эффективность компании.
10. Измените значения в ячейках Доход_на_один_заказ и Почтовые_расходы. Может оказаться, что все значения в таблице подстановки при этом станут отрицательными. Увеличьте предполагаемый процент ответов, количество разосланных материалов, либо снизить расходы на печать. Поэкспериментируйте
Зачетные задания
Вариант 1
Задача 1.Рассчитайте, какую сумму необходимо положить на депозит под 12% годовых, чтобы через 10 лет она выросла до 980000 руб. при полугодовом начислении процентов. Ответ округлите до копеек. А если первоначально положить 300000 руб., то какую сумму следует ожидать через 10 лет?
Задача 2.Создать и отформатировать таблицу, используя следующие данные:
а) Найти кредиторов, у которых годовая процентная ставка больше среднего значения этого показателя по всему списку. Посчитать их с помощью функции СЧЕТЕСЛИ.
б) Найти кредиторов, которые взяли кредит на срок, превышающий три года.
в) Найти кредитора, который взял наименьший кредит.
г) С помощью условного форматирования выделить максимальную и минимальную сумму кредита.
д) Построить график «сумма кредита от годовой ставки».
е) Посчитать общую сумму выданного кредита под 22% годовых с помощью функции СУММЕСЛИ.
Задача 3.Рассчитать Единый социальный налог за первый квартал текущего года для каждого сотрудника и по предприятию в целом.
Пояснения. Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Оклад» (3), «Иждивенцы» (4), «Налогооблагаемая база за квартал» (5), «Налог на доходы для физических лиц (НДФЛ)» (6), Отчисления в: «Федеральный бюджет» (7), «Фонд социального страхования» (8), « Федеральный фонд обязательного медицинского страхования» (9); «Территориальные фонды обязательного медицинского страхования» (10), «Итого единый социальный 1» (11), «Итого единый социальный 2» (12).
Данные граф 1,2,3,4 задаются самостоятельно. Значение графы 5 вычисляется как утроенное значение графы 3. Значение графы 6 вычисляется по ставке 13%. Графы 7,8,9,10,11 вычисляются с помощью функции ПРОСМОТР, ставки налогов содержатся в таблице. Значение графы 12 рассчитывается как сумма граф 7-10. Таблица.
Таблица - «Ставки единого социального налога предприятия»
Вариант 2.
Задача 1.Какова сумма долга через 26 месяцев, если его первоначальная величина 5000$, процентная ставка – 20% годовых, начисление поквартальное.
· Рассчитать значение долга через 0, 1,2,3,4,5 лет при годовых процентных ставках 5%, 10%, 15%, 20%, 25% .
· Построить графики зависимости долга от срока.
Задача 2.Туристическое агентство «Академия-тур» реализует путевки за границу на следующих условиях:
1) Курс доллара равен 25,6 руб. Вычислить стоимость в рублях.
2) Найти общую прибыль туристического агентства
3) Определить тур, приносящий максимальную прибыль. Используя условное форматирование, выделить его красным цветом.
4) Помощью функции СУММЕСЛИ подсчитать количество авиатуров.
5) Построить и отформатировать диаграмму, отражающую прибыль по каждому туру.
Задача 3.Для операциониста обменного пункта валют требуется разработать таблицу купли-продажи валют и рассчитать прибыль обменного пункта.
Пояснения. Создайте таблицу, состоящую из граф: «Код валюты» (1), «Наименование валюты» (2), «Количество купленной валюты» (3), «Количество валюты проданной» (4), «Курс валюты ЦБ» (5), «Курс покупки» (6), «Курс продажи» (7), «Прибыль от покупки» (8), «Прибыль от продажи» (9), «Общая прибыль» (10), «Налог на прибыль» (11), «Прибыль обменного пункта» (12). Данные для граф 1, 3, 4 задайте самостоятельно. Значения граф 2, 5 определите с использованием таблицы «Курс валют» и функций ВПР и ГПР. Графы 6, 7 рассчитайте в соответствии с инструкцией (моржа составляет не более 10%). Подсчитайте общую сумму по графам 10, 11, 12.
Вариант 3.
Задача 1.Рассматриваются две схемы вложения денег на три года: в начале каждого года под 24% годовых или в конце каждого года под 36 % годовых. Каждые полгода вносится по 4000 руб. Какая схема выгоднее?
Задача 2.Фирмы «ТРЭК» и «ВЕКТОР» закупили канцтовары семи наименований в фирме «САЛЮТ». Фирма «САЛЮТ» предоставляет скидки при покупке товара на сумму более 3000 руб. – 5%, а на сумму более 5000 руб. – 7%.
a) Для каждой фирмы составьте и заполните таблицы, содержащие следующие колонки: Наименование товара, Цена в $, Количество, Стоимость в $, Стоимость в рублях.
b) Используя функцию «Автосумма», найдите сумму закупок для каждой фирмы.
c) Определите размер скидок, используя логическую функцию «ЕСЛИ».
d) Оформите таблицу, отформатируйте заголовки колонок.
e) Определите, какая фирма получила наибольшую скидку.
f) Постройте круговые диаграммы, отражающие долю каждого товара в общей сумме для каждой фирмы.
Задача 3.Рассчитать Единый социальный налог за первый квартал текущего года для каждого сотрудника и по сельскохозяйственной организации в целом.
Пояснения. Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Оклад» (3), «Иждивенцы» (4), «Налогооблагаемая база за квартал» (5), «Налог на доходы для физических лиц (НДФЛ)» (6), Отчисления в: «Федеральный бюджет» (7), «Фонд социального страхования» (8), « Федеральный фонд обязательного медицинского страхования» (9); «Территориальные фонды обязательного медицинского страхования» (10), «Итого единый социальный 1» (11), «Итого единый социальный 2» (12).
Данные граф 1,2,3,4 задаются самостоятельно. Значение графы 5 вычисляется как утроенное значение графы 3. Значение графы 6 вычисляется по ставке 13%.
Графы 7,8,9,10,11 вычисляются с помощью функции ПРОСМОТР, ставки налогов содержатся в таблице. Значение графы 12 рассчитывается как сумма граф 7-10.
Таблица - «Ставки единого социального налога сельскохозяйственных производителей»
Вариант 4.
Задача 1.Ипотечный кредит в размере 58500 руб., выданный под 10% годовых, гасится ежегодными выплатами по 10900 руб. Сколько лет будет погашаться кредит? Какими должны быть выплаты, чтобы уменьшить срок платежа на один год?
Задача 2.Имеются данные
1) Рассчитать доходы (Д), расходы (Р) и чистую прибыль (П), равную П=(Д-Р)(1-Н), где Н – налог на прибыль, исчисляемый по формуле:
Н=20%, если доход меньше или равен 500; Н=30%, если доход меньше или равен 1000; Н=40%, если доход меньше или равен 1500; Н=50% в остальных случаях.
2) С помощью Условного форматированиязакрасить синим цветом ячейки, в которых доход больше 500 и меньше 1000.
3) Подсчитать с помощью функции СУММЕСЛИ выручку при продаже аспирина.
4) Постройте круговые диаграммы, отражающие долю каждого наименования товара в общем объеме реализаций.
Задача 3.Рассчитать обязательные взносы в пенсионный фонд за первый квартал текущего года по каждому сотруднику и по предприятию в целом.
Пояснения. Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Дата рождения» (3), «Оклад» (4), «База для начисления страховых взносов за квартал» (5); Отчисления : «Для лиц 1966 г.р. и старше» (6), «Для лиц 1967 г.р. и моложе – страховая часть трудовой пенсии» (7), «Для лиц 1967 г.р. и моложе – накопительная часть трудовой пенсии» (8), «Итого отчислений» (9).
Данные граф 1,2,3,4 задаются самостоятельно. Значение графы 5 вычисляется как утроенное значение графы 4.
Графы 6,7,8 вычисляются с помощью функции ПРОСМОТР, ставки налогов содержатся в таблице.
Таблица - «Ставки налоговых отчислений в Пенсионный фонд»
Список литературы
1. Гобарева Я.Л. Технология экономических расчетов средствами MS EXCEL: учебное пособие / Я.Л. Гобарева, О.Ю. Городец-кая, А.В. Золотарюк. – М.: КНОРУСС, 2006 –344 c.
2. Лавренов С.М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2006. – 336 c.
3. Уокенбах, Д. Microsoft Office Excel 2007. Библия пользователя.: Пер. с англ. / Д. Уокенбах. – М. : ООО «И.Д.Вильямс», 2008. – 816 с. : ил.