Поиск необходимых сведений в базе данных.

Выделите получившуюся таблицу. Во вкладке «Вставка» выберите пункт «Таблица». Создайте таблицу с заголовками.

 

Работа с фильтром:

Найдите всех сотрудников, родившихся до 01.01.1985

Найдите всех сотрудников женского пола

Найдите всех сотрудников, проживающих в Невском (любом другом) районе города

Найдите сотрудников, чья фамилия начинается на «Р»

Найдите всех сотрудников, занимающих определенную должность

Найдите всех сотрудников с окладом больше 15.000р; меньше 20.000р.

 

Содержание отчета

- Титульный лист

- Описание задачи

- Таблица в соответствии с заданием

- Скриншоты работы фильтров

- Выводы.

 

Лабораторная работа №3

Логические функции в MS Excel

Цель работы: освоить работу функций «ЕСЛИ» и «СУММЕСЛИ»

Теоритические сведения

Рассмотрим функции MS Excel ЕСЛИ и СУММЕСЛИ.

 

Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. Например, формула =ЕСЛИ(A1>10,"Больше 10","10 или меньше") возвращает строку "Больше 10", если значение в ячейке A1 больше 10, и "10 или меньше", если оно меньше или равно 10.

Синтаксис

ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])

 

Функция ЕСЛИ имеет аргументы, указанные ниже.

Лог_выражение — обязательный аргумент. Любое значение или выражение, дающее в результате значение ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.

Значение_если_истина — необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА. Например, если данный аргумент — строка "В пределах бюджета", а аргумент лог_выражение соответствует значению ИСТИНА, функция ЕСЛИ возвращает текст "В пределах бюджета". Если аргумент лог_выражение соответствует значению ИСТИНА, а аргумент значение_если_истина опущен (т. е. после аргумента лог_выражение есть только запятая), возвращается значение 0. Чтобы отобразить слово ИСТИНА, используйте логическое значение ИСТИНА в качестве этого аргумента.

Значение_если_ложь — необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ, Например, если данный аргумент — строка "Превышение бюджета", а аргумент лог_выражение соответствует значению ЛОЖЬ, функция ЕСЛИ возвращает текст "Превышение бюджета". Если аргумент лог_выражение соответствует значению ЛОЖЬ, а аргумент значение_если_ложь опущен (т. е. после аргумента значение_если_истина нет запятой), возвращается логическое значение ЛОЖЬ. Если аргумент лог_выражение соответствует значению ЛОЖЬ, а значение аргумента значение_если_ложь опущено (т. е. в функции ЕСЛИ нет запятой после аргумента значение_если_истина), возвращается значение 0.

 

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанным критериям. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать указанную ниже формулу.

=СУММЕСЛИ(B2:B25;">5")

В данном примере на соответствие критериям проверяются суммируемые значения. При необходимости можно применить критерии к одному диапазону и просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".

 

Синтаксис

СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])

 

Функция СУММЕСЛИ имеет аргументы, указанные ниже.

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

Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку, текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, критерий можно выразить как 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().

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

Диапазон_суммирования. Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется критерий).

Примечания

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

Если диапазон равен	и диапазон суммирования равен	то используются ячейки
A1:A5	B1:B5	B1:B5
A1:A5	B1:B3	B1:B5
A1:B4	C1:D4	C1:D4
A1:B4	C1:C2	C1:D4

В аргументе критерий можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак (или звездочку), следует ввести перед ним тильду (~).

 

Порядок выполнения работы

1. Запустите программу Microsoft Excel (ПУСК – ПРОГРАММЫ – EXCEL). На Листе 1 создайте таблицу в соответствии с заданием.

2. Произведите расчеты, указанные в задании.

Содержание отчета

- Титульный лист

- Описание задачи

- Таблица в соответствии с заданием

- Описание приведенных расчетов

- Выводы.

 

Варианты заданий:

 

Вариант 1. Определите товарооборот за год, рассчитайте сумму налога учитывая, что, если сумма товарооборота не превышает или равна 15 000 000 руб. налоговая процентная ставка равна 13%, иначе - 20%. Рассчитайте сумму налогов и определите чистую прибыль.

Филиал	I кв.	II кв.	III кв.	IV кв.	Товарооборот за год	Ставка, %	Прибыль без налоговых вычетов	Сумма налогов
Итого

Чистая прибыль предприятия _________

Задача 2. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада), сумму удержанного налога на доходы физических лиц (ПН) равного:

12%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ и

15%, если сумма оклада и премии больше 20-кратного размера МРОТ, но меньше или равна30-кратному размеру МРОТ,

если больше – 20%.

МРОТ
Коэффициент
Nп/п	Ф.И.О.	Должность	Разряд	Оклад	Премия	Удержано ПН	К выдаче
	Иванов	Продавец
	Петров	Менеджер
	Сидоров	Грузчик

Задача 3. Рассчитайте сумму премии:

50% от оклада, если сумма оклада не превышает 4000 руб.,

40% от оклада – если равен или более 40000 руб.,

сумму удержанного налога на доходы физических лиц (ПН) равного 20% от премии и оклада, сумму к выдаче.

МРОТ
Nп/п	Ф.И.О.	Должность	Разряд	Оклад	Премия	Удержано ПН	К выдаче
	Иванов	Директор
	Петров	Менеджер
	Сидоров	Бухгалтер

 

Задача 4. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада), сумму удержанного налога на доходы физических лиц (ПН) равного:

20%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ,

25%, если сумма оклада и премии больше 20-кратного размера МРОТ.

МРОТ
Nп/п	Ф.И.О.	Должность	Разряд	Оклад	Премия	Удержано ПН	К выдаче
	Иванов	Директор
	Петров	Продавец
	Сидоров	Бухгалтер

 

Раздел II

Проектирование базы данных