Добавить материал и получить бесплатное свидетельство о публикации в СМИ
Эл. №ФС77-60625 от 20.01.2015
Инфоурок / Информатика / Другие методич. материалы / Практическая работа "Excel. Обработка табличной информации. Логические функции"

Практическая работа "Excel. Обработка табличной информации. Логические функции"


  • Информатика

Поделитесь материалом с коллегами:

Тема: Excel. Обработка табличной информации.


Цель работы: получить практические навыки работы с :

  • логическими функциями,

  • функциями даты,

  • абсолютными ссылками,

  • освоить технологию динамического обмена данными (DDE) между Excel и Word.

Примечание: работу выполнить в одном файле (рабочей книге) на разных листах.


Часть 1. Использование логических функций


Пусть налог исчисляется по прогрессивной шкале следующим Образом: с заработной платы, не превышающей 1000 руб., налог составляет 12%, а с части зарплаты, превышающей 1000 руб., — налог 20% от этой части.

1. Вначале определим исходные данные задачи: фамилии работников (текст) и размер заработной платы (число с двумя цифрами в дробной части).

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

2. Для выполнения расчетов запустить Excel, и на первом рабочем листе опишите структуру таблицы и введите исходные данные (табл. 1)

hello_html_m40cdc308.png

3. «Лист2» переименовать в «Расчет зарплаты»

4. В ячейку СЗ необходимо поместить величину подоходного налога, вычисляемого по прогрессивной шкале, для этого выделить ячейку СЗ и ввести формулу =ЕСЛИ(ВЗ<=1000;ВЗ*0,12;1000*0,12+(ВЗ-1000)*0,2). В этой формуле проверяется условие В3<=1000. Если условие соблюдается, то налог вычисляется по формуле В3*0,12. Если условие ложно, то налог вычисляется по формуле 1000*0,12+(ВЗ-1000)*0,2 (12% от суммы зарплаты в 1000 рублей + 20% от суммы, превышающей 1000 рублей).

5. В ячейку D3 ввести формулу =ВЗ-СЗ для определения суммы разности зарплаты и налога.

6. Скопировать формулы из диапазона C3:D3 в диапазон C4 : D6. В ячейку В7 ввести формулы суммирования результата по столбцу В, для чего, выделив ячейки ВЗ : В6, щелкнуть кнопку в панели инструментов Стандартная. Скопировать формулу вычисления суммы столбца из В7 в C7:D7.

Таблица будет иметь следующий вид:

hello_html_m72d5a70a.png

7. Переименуйте Лист №, введя новое имя Расчёт зарплаты

8. Сохранить файл на диске О: с именем Работа в Excel


Часть 2. Использование функций даты

1. Запустить Excel и просмотреть справочную информацию о назначении и синтаксисе функций даты и времени. Для этого, вызвав справку Excel, на вкладке поиск задать образ поиска «функции даты», затем в списке найденных разделов выбрать раздел Функции работы с датой и временем и щелкнуть кнопку Показать. Пользуясь кнопками ссылок, просмотреть справки по функциям ДАТА, ДЕНЬ, МЕСЯЦ, ДЕНЬНЕД, СЕГОДНЯ, ТДАТА. Для возврата на предыдущий экран справок использовать кнопку Назад. Закрыть окно справки.

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

3. В ячейке А1 ввести заголовок таблицы «Календарь». Во второй строке в ячейки А2, В2, С2 ввести названия граф: «Дата», «Номер дня», «Название дня и месяца».

4. Задать в ячейке A3 формулу =ДАТА(2010;10;01) для ввода первой даты нужного года. В ячейке А4 задать формулу =АЗ+1 для вычисления даты следующего дня. Скопировать формулу из А4 в диапазон А5:А39. Получится календарь на 2005 год.

4. В ячейку ВЗ ввести формулу определения дня недели =ДЕНЬНЕД(АЗ;2). (Согласно параметру 2 порядок дней в неделе следующий: понедельник - 1, вторник - 2, среда - 3, ..., воскресенье — 7). Скопировать эту формулу из ВЗ в диапазон В4:В39. В столбце В будут выведены порядковые номера дней недели за весь год.

5. Используя справочную систему Excel, найти подсказку по функции ТЕКСТ. Для этого вызвать справку и на вкладке Поиск задать образ поиска «текст», затем в списке найденных разделов выбрать раздел ТЕКСТ и щелкнуть кнопку Показать. После просмотра справки закрыть окно любым из стандартных способов.

6. В ячейку СЗ ввести формулу =ТЕКСТ(АЗ; «ДДДД МММММM»), которая по дате определит текстовое название дня недели и месяца. Скопировать формулу из СЗ в диапазон С4:С39. В столбце С будут выведены названия дней недели и месяцев за весь год.

7. Выделить таблицу и оформить ее, выбрав в меню Формат команду Ячейки. На вкладке Граница выбрать тип линии и включить внешние и внутренние границы, на вкладке ВИД выбрать цвет заливки и щелкнуть кнопку Ок.


hello_html_29e99bcc.png


Рисунок 1. Таблица «КАЛЕНДАРЬ»


9. Защитить таблицу, оставив возможность пользователю изменять год, на который создается календарь. Для этого предварительно сняв за­щиту с ячейки A3, выбрать в меню Сервис команду Защита, затем — Защитить лист. Проверить действие защиты, попробовав ввести в ячейку A3 другую дату, ввести данные в другие ячейки.

10. Переименуйте Лист3, введя новое имя Календарь.


Часть 3. Абсолютные ссылки

На третьем рабочем листе (Лист3) составить таблицу распределения доходов в трудовом коллективе в соответствии с трудовым вкладом каждого работника.

Допустим, что трудовой вклад каждого работника измеряется коэффициентом трудового участия (КТУ), который прямо пропорционален квалификации работника и времени его работы. Конечно, реальная задача расчета участия каждого работника в трудовой деятельности коллектива значительно сложнее.

1. Сначала нужно определить исходные данные задачи: величину распределяемой суммы дохода (число), фамилии работников (текст), уровень квалификации (разряд — целое число) и время работы. В качестве рассчитываемых данных в таблице будет значение КТУ для каждого работника и суммарное значение КТУ всех работников (число) и сумма выплаты каждому работнику (число).

2. Запустить Excel, переименовать Лист 3 в КТУ, на листе КТУ описать структуру таблицы и ввести исходные данные (табл. 3).

3. Ввести в ячейку D4 формулу расчета КТУ =В4*С4. Скопировать формулу из ячейки D4 в диапазон D5:D7.

4. В ячейку D8 ввести формулу суммы диапазона D4 : D7, для чего выделив диапазон D4:D7, щелкнуть кнопку “Автосумма” на панели инструментов “Стандартная”.

5. Ввести в ячейку С2 величину распределяемого дохода, например, 10000.

6. Ввести в ячейку Е4 формулу расчета выплаты = $C$2/$D$8*D4. (Выплата каждому работнику равна частному от всей суммы доходов коллектива и суммы КТУ всех работников, умноженному на величину КТУ данного работника.) Обратите внимание, что адреса ячеек С2 и D8 записаны в абсолютной форме, т. е. они не изменятся при копировании формулы из ячейки Е4 в Е5:Е7.

hello_html_3359465d.png

Примечание. Для создания абсолютной ссылки на ячейку С2, D8 нужно при вводе формулы щелкнуть на данной ссылке клавишей F4.

7. Задать финансовый формат для значений данных в ячейках Е4:Е7, для чего выделив этот диапазон, выбрать в меню Формат опцию ячейки, а затем на вкладке Число окна Формат ячеек выбрать вариант числового формата: Финансовый, после чего указать число десятичных знаков 2, а в поле Обозначение выбрать р. и щелкнуть кнопку ОК.

8. Изменить ширину столбца Е, чтобы в нем правильно отображались выплаты в финансовом формате.

9. Проверить действие таблицы, изменяя данные в ячейках С2, В4:С7.

10. Построить диаграмму, отображающую долю выплат каждому работнику от общей суммы доходов. Для этого выделить диапазон ячеек A4:D7 и щелкнуть кнопку “Мастер диаграмм” в панели инструментов “Стандартная”. В начале построения диаграммы выбрать тип Разрезная круговая диаграмма и щелкнуть кнопку Далее. На этапе определения источни­ка данных диаграммы выбрать для отображения в диаграмме данные из рядов в столбцах, уточнить диапазон. На вкладке Ряд в поле Имя задать диапазон данных, отображающих название Распределение доходов в коллективе.

11. Изменить формат области диаграммы, для чего щелкнуть ПКМ на диаграмме, в контекстном меню выбрать команду Формат области диаграммы. Выбрав вкладку Вид, щелкнуть кнопку Способы заливки. В окне Заливка выбрать вкладку Текстура, затем щелкнуть кнопку с текстурой (например, белый мрамор). Щелкнув кнопку ОК, закрыть окно Заливка, а затем щелкнуть ОК для закрытия окна Формат области диаграммы. Просмотреть вид полученной диаграммы.

12. Сохранить результат работы.



Часть 4. Логические функции – ЕСЛИ, И, ИЛИ.


При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов в зависимости от выполнения или невыполнения одного или не­скольких условий.

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



3







4

Наименование

Код ед.изм.

Кол-во

Цена

Стоимость

5

1

Сахар

1

500

20

?

6

2

Мука

2

10

350

?

7

3

Пшено

1

200

14

?

8

4

Масло сл.

3

350

23

?

9

5

Томатная паста

3

800

18

?


Для решения таких задач применяют условную функцию ЕСЛИ.
Эта функция имеет формат:


ЕСЛИ(<логическое выражение>; <выражение1>; <выражение2>).


Первый аргумент функции ЕСЛИ — логическое выражение (в частном случае, условное выражение), которое принимает одно 1 из двух значений: «Истина» или «Ложь» (1 или 0). В первом случае


ЕСЛИ принимает значение выражения 1, а во втором — значение выражения 2. В качестве выражения 1 или выражения 2 можно использовать выражение, также содержащее функцию ЕСЛИ. В этом случае она называется вложенной функцией ЕСЛИ.

Вернемся к примеру 1. Если количество задано в кг, стоимость С рассчитывается по формуле

С=Q*Ц,

где Q — количество, кг; Ц — цена (р./кг).

Если количество задано в тоннах, стоимость рассчитывается по формуле

С= Q1*1000*Ц,

где Ql — количество продукции, т.

Пусть в ячейке С5 помещается код единицы измерения коли­чества продукции, который принимает следующие значения: 1 — кг; 2 — т; 3 — шт. В ячейке D5 помещается количество продукции, в ячейке Е5 — Цена. В ячейку F5 необходимо поместить стоимость товара. Тогда в эту ячейку мы можем записать функцию

ЕСЛИ(С5=1; D5*E5; ЕСЛИ(С5=2; D5*1000*E5; 0)).

Здесь логическим выражением является условие С5=1. Если С5=1, то условие выполнено, и значение логического выражения — «Истина». Поэтому функция ЕСЛИ, записанная в ячейке F5, при­мет значение второго аргумента, т.е. D5*E5. Если же C51, то ус­ловие не выполнено, и значение логического выражения — «Ложь», и поэтому функция ЕСЛИ примет значение третьего аргу­мента, т.е. вложенной функции ЕСЛИ (С5=2; Д5*1000*Е5; 0). А чему же равно это значение? Оно зависит от выполнения усло­вия С5=2. Если это условие выполнено, то значением вложенной функции будет ее второй аргумент, т.е. D5*1000*E5, если не вы­полнено — то третий аргумент, равный нулю.

Число вложенных функций ЕСЛИ не должно превышать семи.

Если условий много, то записывать вложенные функции ЕСЛИ становится неудобно. В этом случае на месте логического выраже­ния мы можем указать одну из двух логических функций: И (AND) и ИЛИ (OR).

Формат функций одинаков:

И(<логическое выражение1>, <логическое выражение2>,...);

ИЛИ(<логическое выражение 1>, <логическое выражение2>,...).

Функция И принимает значение «Истина», если одновремен­но истинны все логические выражения, указанные в качестве ар­гументов этой функции. В остальных случаях значение И — «Ложь». В скобках можно указать до 30 логических выражений.

Функция ИЛИ принимает значение «Истина», если истинно хотя бы одно из логических выражений, указанных в качестве


аргументов этой функции. В остальных случаях значение ИЛИ — «Ложь».

5.2 Изменим условие формирования расчетной таблицы. В ней могут быть товары, количество которых измеряется в килограммах или в штуках, и соответственно цена за товар может быть в рублях за один кг или за одну штуку.

В этом случае стоимость определяется только в том случае, если выполнено одно из условий: либо заданы количество в кг или тоннах, цена в рублях за один кг, либо заданы и количество в -штуках, и цена в рублях за одну штуку. Для решения этой задачи, кроме кода единицы измерения количества продукции, необхо­димо ввести код единицы измерения цены. Выделим для этого клетку G5. Примем, что код единицы измерения цены имеет зна­чения: 1 — р./кг; 2 — р./шт.



3








4

Наименование

Код ед.изм.

Кол-во

Цена

Стоимость

Код ед.
измер. цены

5

1

Сахар

1

500

20

?

1

6

2

Мука

2

10

350

?

3

7

3

Пшено

1

200

14

?

1

8

4

Масло сл.

3

350

23

?

2

9

5

Томатная паста

3

800

18

?

2


Тогда в ячейку F5 можно записать функцию ЕСЛИ, содержа­щую двукратное вложение функции ЕСЛИ:

ЕСЛИ(И(С5=1; G5=l); D5*E5; ЕСЛИ(И(С5=2; G5=l);

D5*1000*E5; ЕСЛИ(И(С5=3; G5=2); D5*E5; 0))).

Таким образом, если одновременно С5=1 и G5=l (кг и р./кг) или одновременно С5=3 и G5=2 (шт. и р./шт.), стоимость равна D5*E5; если одновременно С5=2 и G5=l (тонны и р./кг), стоимость равна D5*1000*E5; в остальных случаях она равна нулю.

Подобным же образом можно использовать и функцию ИЛИ.








Автор
Дата добавления 30.08.2016
Раздел Информатика
Подраздел Другие методич. материалы
Просмотров303
Номер материала ДБ-171419
Получить свидетельство о публикации

Похожие материалы

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