Задание: "Справочник"

Задание: "Справочник"

Вам необходимо создать электронную записную книжку.

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

2. Далее необходимо таблицу заполнить.

ВЫЧИСЛЯЕМЫЕ поля: Возраст, Сколько лет до пенсии

Возраст: 

а) В категории Математические берем функцию ЦЕЛОЕ, т.к. нам необходимо вычислить, сколько полных лет.

б) Раскрываем список вложенных функций, в строке формул и выбираем функцию СЕГОДНЯ (или Другие функции …- Категория - Даты и времени)

в) Т.к. в функции СЕГОДНЯ аргументов нет,  выходим назад в функцию ЦЕЛОЕ. В строке формул по функции ЦЕЛОЕ щелкнуть мышкой. 

г) В строке аргументов функции ЦЕЛОЕ, дописываем формулу: от функции СЕГОДНЯ вычитаем дату рождения. 

д) Разность делим на среднюю продолжительность года (365 дней) 

(СЕГОДНЯ()-D2)/365

 Формат этих ячеек должен быть числовым

Сколько лет до пенсии:

Для заполнения этого столбца, необходимо воспользоваться следующей информацией:

ЕСЛИ в столбце пол стоит "М",

то от 60 лет отнять возраст,

в противном случая 55-возраст (т.к. это женщина, а она  идет на пенсию в 55 лет)

3. Заполнить таблицу еще несколькими сотрудниками.
     

Желательно внести в таблицу своих одноклассников 

или друзей (ФИ можно изменить)
ВАЖНО, чтобы год рождения был ваших сверстников (т.е. младше 18-20 лет)

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

4. Необходимо отсортировать по алфавиту и применить новую нумерацию по порядку.

Встать на заголовок  – Фамилия, имя – ПКМ – Сортировка по возрастанию

или

Встать на заголовок  – вкладка Данные – кнопка Сортировка

Проверьте, чтобы у первого сотрудника был порядковый номер 1 у второго 2. Выделить 1 и 2 и автозаполнение протянуть до последнего сотрудника, кол-во: 18 человек.

5. Посчитайте количество служащих по трем возрастным категория: до 25 лет; от 25 до 40; после 40 лет.

      

Количество служащих  возрастной группы от 25 до 40 определяется вычитанием из общего количества слушателей (используется функция СЧЁТ, категория Статистические) тех, кому <25 и > 40.

6. Посчитайте средний возраст слушателей.

Есть еще возможность находить информацию через Фильтры.

1.  Выделите шапку в таблице A1:K1; или встаньте в область таблицы 2.  Выбери на вкладке Данные– Фильтр      У таблице в шапке появились маркеры 3. Раскройте фильтр в столбце Город 4. Скиньте все галочки (Выделить все) 5.Установите галочку на  Новокузнецк:

Если необходим список из 2-х городов  Новокузнецк и Кемерово:

1.  Раскройте список города; 2.  У Вас уже стоит галочка на Новокузнецке 3. Установите фильтр (отметьте галочками)  Кемерово, Новокузнецк 4.   ОК Получилось? Молодцы! Чтобы вернуть весь список, можно раскрыть список фильтра в столбце Город и выбрать - Выделить все А теперь необходимо выполнить 2 задания.

Задание_1: Нам нужен список сотрудников: живущих в России, до пенсии >= 20 лет и с контрактом > 25 000 рублей.

Получившийся список (таблицу) скопируйте и вставьте на новый лист, который назовите "задание 1". Отформатируйте фрагмент таблицы.

Вернитесь на лист с данными.

Верните весь список

Задание_2: Выведите список сотрудников с годом рождения с 1960 по 1970.
Оставьте данный список (не убирайте фильтр)

Задание: "Успеваемость"

Задание:  "Успеваемость"

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

1. Наберите таблицу, приведенную на рисунке. Вручную внесены оценки за четверти и экзамен. Отформатируйте документ. (за год и ср.итоговый балл - будут формулы)

2. В ячейке F4 (оценка за год по русскому) – это формула среднего значения четвертных оценок. 

Можно поставить =, затем просуммировать оценки и разделить на 4, но ЛУЧШЕ воспользоваться мастером функций СРЗНАЧ (среднее значение). 

Скопируйте полученную формулу во все остальные ячейки столбца. 

Уменьшите разрядность, до целых единиц.

3. В ячейке Н4 (средний итоговый балл по русскому языку) – считается по следующей формуле:

Запустите мастер функций, выберите функцию ЕСЛИ (категория - логические)

Если оценка за год выше или равна оценке за экзамен,

то ставится оценка за год,

иначе ставится среднее значение, (вычисленное по оценкам за четверти и за экзамен).

Как подсчитать среднее значение,

которое будет записано в третьей строке:

В строке формул (самая левая позиция) раскрыть список функций и выбрать в списке СРЗНАЧ, 

в ней отметить те ячейки, 

которые участвуют в подсчете среднего значения.

  

Первый блок - оценки за четверти

Второй -  оценка за экзамен.

Если вы в строке формул нажмете ЛКМ по функции ЕСЛИ, то результат будет следующий:

Нажать ОК.

Скопируйте полученную формулу во все остальные ячейки столбца. 

Уменьшите разрядность, до целых

Задание: "Раскладка меню"

Задание: откройте файл "Использование функций и Постоянная ячейка", лист "Раскладка меню" или наберите и оформите по образцу данное задание

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

1. Оформите таблицу. Подготовьте определенную ячейку, в которой будет вводиться количество порций. (С1 = например 5)

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

3. В ячейке С4 введите = В4*C1

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

Встаньте в ячейку С4 и в строке формул отразилась наша формула .

ЛКМ щелкните в строке формул  у адреса С1 и нажали на клавишу F4. 

Ячейка С1 стала постоянной. 

Зафиксируйте формулу и скопируйте.

5. По изменяйте в ячейке С1 общее количество порций. Общая раскладка продуктов изменяется.
6. Не забудьте обрисовать таблицу. 

Дополнительное задание: сделать обязательно.

Давайте изменим немного формулу и посмотрим изменится что-нибудь.

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

Сделайте это.

1)  Встаньте в первую формулу, которую вы писали: это ячейка С4.

2) В строке формул удалите знак постоянства у столбца С, результат  =В4 * С$1

3) Перепишите формулу для остальных продуктов (копируете формулу вниз [по старым формулам, не удаляя их])

4) Проверьте результат, изменился?

5) Конечно нет

Задание: "Числа"

Задание:  откройте файл "Использование функций и Постоянная ячейка" лист «Числа» или наберите и оформите по образцу данное задание

Дана последовательность чисел: 25; -61; 0; -82; 18; -11; 0; 30; 15; -31; 0; -58; 22.

Заполните ячейки К5:К14 соответствующими формулами

В ячейку А1 введите текущую дату, используя мастер функций (категория Дата и время, функция - Сегодня). Вы увидите, это значит, что в ячейку информация не вошла.

Выделите блок (A1:M1) и объедините ячейки .

Здесь перечислены все функции которые вам будут необходимы (Категория Статистические СЧЕТ, СЧЕТЕСЛИ, МИН, MАКС, СРЗНАЧ, и Математические СУММ, СУММЕСЛИ).

Для выполнения данного задания функции можно воспользоваться КНОПКОЙ мастер функций  или лентой ФОРМУЛЫ

Рассмотрим применение функции СЧЕТ для нахождения общего количества чисел:

1.   Встаньте в ячейку K5. 2.   Вызовите мастер функций и в категории Статистические, выберите функцию СЧЕТ. 3.   Мастер вам предложит посчитать данные, стоящие в диапазоне K3:K4. 4.   Необходимо заменить диапазон на A3:M3. Для этого выделите в таблице эти ячейки, не закрывая окна функции СЧЕТ и нажмите OK.
Далее Вам необходимо определить, сколько положительных чисел в ряду. Вы можете воспользоваться функцией СЧЕТЕСЛИ (Категория Статистические) Количество отрицательный числе и чисел равных 0 прописывается по аналогии, только критерий зависит от условий.
Функции для подсчета максимального значения, минимального и среднего значения можно воспользоваться раскрытием кнопки Автомуммы (на вкладке ГЛАВНАЯ в группе РЕДАКТИРОВАНИЯ) !Не забывайте правильно указывать диапазон ячеек, которые вы анализируете (это всего A3 : M3)
Последние две функции, категория математические. Диапазон все тот же A3 : M3, критерий суммирования, числа > 0, а вот если диапазон суммирования совпадает с диапазоном, то его можно не указывать. В последней функции меняется только критерий, т.к. необходимо подсчитать сумму отрицательных чисел.

Задание: "Ремонт"

Задание:  Файл «Ремонт»

Создать таблицу расчёта строительных материалов для ремонта квартиры, подобную той, какая прописана в таблице на рисунке

1. Оформите таблицу по образцу

К1 – 0,5;

L1 – сначала пробел, потом черточка, затем слово (иначе Excel понимает информацию как формула и сообщает об ошибке)

А2 – Образцы обоев (потом можно объединить);

В3 : Е3 – пишем текст, затем выделяем его и задаем перенос по словам, по центру (по горизонтали и вертикали),

если перенос не получился – выделяем столбцы (В, С, D) и уменьшаем ширину принудительно;

в ячейках А4 : А9 – прописываем комнаты, оформляем жирным; по левому краю, по середине;

чтобы строки сделать шире: выделяем строки по названиям с 4 по 12 и вручную делаем её шире

2. Рисунки и вид обоев оформите через: Вставка – Рисунок (или Клип - Упорядочить клипы ... - Коллекции MSOffice) 

нарисовав прямоугольник, создать обои через Формат - Заливка фигуры - Текстура

3. Рассчитайте необходимое количество обоев для каждой отдельной комнаты (ячейки выделены серым цветом)

При подсчете необходимого количества рулонов используйте коэффициент = 0,5 (ячейка К1) на отходы. Коэффициент может меняться, в зависимости от рисунка на обоях.

В ячейках F4, G5, H6, I7, J8, K9 введите соответствующие формулы - 

=(длина + ширина) * 2 * высота / длина рулона * коэффициент

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

4. Рассчитайте количество рулонов

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

5. Введите цену за один рулон (строка 11)

6. Рассчитайте стоимость обоев необходимых для каждой  комнаты

7. Подведите итог: Сколько рулонов вам необходимо на каждую отдельную комнату?

В ячейку L4 введите сумму ячеек F4:K4. И скопируйте формулу.

8. Проверьте, правильно ли вы написали все формулы, для этого, в детскую комнату и в спальню выберите 1 рулон любых обоев, для бордюра.

9. У вас должны измениться результаты в 10, 12 строке и в столбце L

10. .Проверьте, чтобы информация входила в один лист.

Задание: "Соревнования"

Задание "Соревнования"

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

• выступление каждого спортсмена оценивают N судей;

• максимальная и минимальная оценки отбрасываются (если их несколько, то отбрасывается по одной такой оценке);

• в зачет спортсмену идет среднее арифметическое оставшихся оценок. Требуется определить итоговые оценки всех спортсменов и оценку победителя.

Если вводить фамилии спортсменов не будете, то можно воспользоваться Автозаполнением– написав только фразу Спортсмен 1 и скопировать вправо +, тоже самое сделать с фразой Судья 1

1. В ячейки В1:Е1 введите фамилии спортсменов, а в ячейки А2:А6 – фамилии судей.

2. Заполните блок В2:Е6 оценками судей для каждого спортсмена (оценки проставляются по десятибалльной системе).

3. В ячейку А8 введите текст «Мах оценка», в ячейку А9 - «Min оценка».

4. В ячейку В8 введите формулу для нахождения максимального числа, в ячейку В9  - минимального числа. Данные функции можно найти в категории Статистические, воспользовавшись мастером функцийили раскрыв на панели инструментов  кнопку

5. Скопируйте содержимое ячейки В8 в ячейки С8:Е8

6. Скопируйте содержимое ячейки В9 в ячейки С9:Е9

7. В ячейку А11 введите текст «Оценка спортсмена».

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

9. Скопируйте содержимое ячейки В11 в ячейки С11:Е11

10. В ячейку А13 введите текст «Оценка победителя».

11. В ячейку В13 введите формулу для нахождения максимального балла из общей оценки спортсменов. Вы получили самый большой балл. 
     Спортсмен с этим баллом и является победителем.

Отразите фамилию победителя в ячейке С13 (используя функцию)

12. Оформите таблицу

Домашнее задание

Домашнее задание для 11 класса

неделя с 07 по 11.04
Создать и отформатировать таблицы ("Продовольственный магазин" и "Товарный счет")
Конспект лекций: темы с 1 по 8 (оформлять в MS Word)
неделя с 14.04 по 18.04
Конспект лекций: темы с 9 по 12
Создать документ "Ремонт"
неделя с 21.04 по 25.04
Повторить темы: Создание формул и использование функций
Создать документ "Числа"
Создать документ "Раскладка меню"
неделя с 28.04 по 02.05
Конспект лекций: темы 13 и 14
Создать документ "Успеваемость"
Создать документ "Справочник" (кому лень набирать содержание таблицы, можно скачать с электронного журнала)

Домашнее задание для 10 класса

на 7.04
Конспект лекций: темы с 1 по 8 (оформлять в MS Word)
Создать и отформатировать таблицу "Товарный счет"
на 14.04
Конспект лекций: темы с 9 по 12
Домашняя контрольная работа (9 задач), скачать в эл.журнале
консультации: среда 16.04 - после 8 урока до 17.00
четверг 17.04 - после 6 урока до 17.00
пятница 18.04 - после 6 урока до 17.00
на 21.04
Повторить темы: Создание формул и использование функций
Домашняя контрольная работа (9 задач) - защита к \ р
на 28.04
на 05.05

1. Выслать 2 работы: Лист1 - "Расчет покупки ПК", Лист2 - "График"

/home/mirit/ruobr.ru/ruobr/schl/media/user/ab1e12557f7f46ae9d32d6ac35936086.docx 

2. Готовимся к тестам: 

а) Устройство ПК - (многие уже сдали)  /home/mirit/ruobr.ru/ruobr/schl/media/user/f4a53660c0754c9ca93492750de092ea.docx 

б) Системный блок. Внешние устройства -  /home/mirit/ruobr.ru/ruobr/schl/media/user/2566d5fe1bc14a03b88ef29eb963a60c.docx 

в) Программное обеспечение. Файловая структура. Вирусы -  /home/mirit/ruobr.ru/ruobr/schl/media/user/45e59b676bcf47f3b70920ac61a6daf9.docx 

3. Подготовить лекции в эл.виде (Word) с 1 по 14 тему:

http://kniazeva.blogspot.ru/2013/11/blog-post_19.html

4. Сдать контрольную работу по Excel, кто не писал (21.04.14) или хочет исправить "2"