Комплексные задания

2.69. Подготовьте данные для службы персонала следующим образом:

1) подготовить таблицу следующей структуры (с указанными наименованиями столбцов):
Фамилия И.О., Дата рождения, Должность (4 - 5 различных должностей в списке, который следует поместить в отдельном столбце), Дата поступления на работу, Пол;

2) заполнить 15 - 20 строк таблицы;

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

4) в отдельном столбце указать Месяц рождения каждого сотрудника;

5) отфильтровать и скопировать на новые листы отдельные списки мужчин и женщин. Каждый список снабдить пояснением, листы подписать. Исходный список восстановить;

6) в отдельном столбце для сотрудников, работающих на предприятии более 10 лет, указать «ветеран»;

7) отфильтровать и скопировать на новый лист список ветеранов, снабдив его пояснением, лист подписать. Исходный список восстановить;

8) подсчитать отдельно количество сотрудников по каждой должности с помощью сводной таблицы;

9) в отдельном столбце для сотрудников, родившихся в указанном месяце, указать «именинник». Предусмотреть возможность изменять месяц, чтобы столбец при этом автоматически изменялся;

10) отфильтровать и скопировать на новый лист список именинников, снабдив его пояснением, лист подписать. Исходный список восстановить.

2.70. Подготовьте данные для социальной службы следующим образом::

1) создать таблицу следующей структуры (с указанными наименованиями столбцов): Фамилия И.О., Дата рождения, Пол, Семейное положение (семейный, одинокий), Количество детей (при отсутствии детей не заполнять);

2) заполнить 15-20 строк таблицы.

3) в отдельном столбце для лиц, имеющих трех и более детей, указать «льготник»;

4) в отдельном столбце подсчитать возраст каждого человека в годах;

5) в отдельном столбце для детей до 16 лет указать «ребенок», для остальных – «взрослый»;

6) отфильтровать и скопировать на новые листы отдельные списки взрослых и детей, лист подписать. Исходный список восстановить;

7) в отдельном столбце для мужчин от 18 до 27 лет указать «призывник»;

8) отфильтровать и скопировать на новый лист список призывников, снабдив его пояснением. Исходный список восстановить;

9) в отдельном столбце для мужчин старше 60 лет и женщин старше 55 лет указать «пенсионер». Если нужно, можно использовать вспомогательные столбцы;

10) отфильтровать и скопировать на новый лист список пенсионеров, снабдив его пояснением. Исходный список восстановить;

11) с помощью сводной таблицы подсчитать отдельно количества женатых и холостых мужчин, замужних и незамужних женщин;

12) с помощью сводной таблицы подсчитать количество мужчин и женщин отдельно;

13) подсчитать количество призывников;

14) с помощью сводной таблицы подсчитать количество пенсионеров (всех вместе и отдельно мужчин и женщин).

2.71. Создать таблицу учета движения заказов:

1) подготовить таблицу следующей структуры (с указанными наименованиями столбцов):
Наименование заказа, Дата оформления, Дата исполнения плановая, Дата исполнения фактическая;

2) заполнить 15-20 строк таблицы так, чтобы даты охватывали период 3 года и приходились на разные месяцы;

3) в отдельном столбце для заказов, у которых фактическая дата исполнения позднее плановой, указать «просрочен»;

4) в отдельном столбце для заказов, у которых дата оформления приходится на прошлый год, указать «прошлогодние»;

5) в отдельном столбце для заказов, у которых дата оформления приходится на апрель, указать «апрель»;

6) в отдельном столбце для заказов, у которых дата оформления приходится на текущий месяц, указать «текущий месяц»;

7) в отдельном столбце для заказов, у которых дата оформления приходится на летние месяцы любого года, указать «лето»;

8) в отдельном столбце для заказов, у которых дата оформления приходится на зимние месяцы любого года, указать «зима»;

9) в двух отдельных столбцах указать год и месяц оформления заказа;

10) с помощью сводной таблицы подсчитать количество заказов по месяцам;

11) в отдельном столбце указать день недели, когда был оформлен заказ;

12) с помощью сводной таблицы подсчитать количество заказов по дням недели.

2.72. Создать многотабличную базу данных для учета продаж:

1) на отдельном листе создать таблицу товаров с указанием цен; столбцам таблицы дать имена «Товар» и «Цена» соответственно, лист подписать;

2) на отдельном листе создать список менеджеров, оформляющих продажи; столбцу дать имя «Менеджер», лист подписать;

3) на отдельном листе создать таблицу покупателей; столбцу дать имя «Покупатель», лист подписать;

4) на отдельном листе создать таблицу продаж следующей структуры: Товар, Менеджер, Покупатель, Цена, Количество, Сумма, Дата; настроить ввод данных в первые три столбца из соответствующих таблиц на других листах (для этого в качестве источника нужно мышью указать столбец с нужного листа книги); для того, чтобы ввод из списка работал во всех ячейках столбца, настройте его сначала для одной ячейки, а затем «растяните» на другие строки;

5) создайте формулу в колонке «Цена» таким образом, чтобы цена на выбранный товар автоматически подставлялась из нужной таблицы. Для этого в Excel существует специальная функция СУММЕСЛИ. Общий вид: СУММЕСЛИ(диапазон; критерий; диапазон_суммирования). В данном случае диапазоном будет столбец «Товар» с первого листа книги, диапазоном суммирования – столбец «Цена» с того же листа, критерием – ячейка с выбранным товаром на листе продаж, т.е. формула в ячейке D2 будет выглядеть примерно так: =СУММЕСЛИ(Товар; B2; Цена), «растяните» ее вниз на остальные строки;

6) столбцы Количество и Дата заполняются вручную; чтобы избежать ошибок, настройте проверку данных;

7) столбец Количество вычислите с помощью формулы;

8) заполните 15-20 строк таблицы;

9) на отдельном листе создайте выборку за текущий месяц;

10) на отдельных листах создайте сводные таблицы Менеджер – Товар и Покупатель – Товар.

2.73. Создание ссылок через листы:

1) на листах 1 – 3 создайте таблицы результатов наблюдений каждый день в течение недели (числа возьмите произвольно), важно, чтобы расположение данных на листах было одинаковым; таблица будет выглядеть примерно так:

2) на листе 4 создайте итоговую таблицу, в которой будут вычисляться суммарные, средние, минимальные и максимальные значения соответствующих величин по всем листам;

3) вычислите колонку «Сумма», формула для нее будет выглядеть примерно так: =СУММ(Лист1! B2; Лист2! B2; Лист3! B2); можно ввести ее вручную или выбрать нужные ячейки мышью, переходя с одного листа на другой;

4) вычислите остальные колонки; обратите внимание – если записать формулу для суммы немного иначе: =СУММ(Лист1! $B2; Лист2! $B2; Лист3! $B2), то можно растянуть ее на другие колонки и изменить функцию, что упростит и ускорит работу.

2.74. Подготовить турнирную таблицу для организации шахматного турнира по круговой системе, когда каждый шахматист играет со всеми остальными по две партии: одну белыми фигурами, другую – черными. За победу участник получает одно очко, за поражение – ноль, а за ничью – пол-очка.

В этом случае турнирная таблица заполняется следующим образом: в строке, отведенной для каждого шахматиста, фиксируются набранные им очки в партиях, где он играет белыми фигурами. Тем самым получается, что в столбце, соответствующем игроку, находятся очки, набранные всеми его соперниками, когда он играл черными (отметим, что это в точности потерянные им очки). Таким образом, число, стоящее в колонке «Очки» не является суммой очков, записанных в строке, т.к. включает в себя еще и данные из столбца

Образец:

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

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

Рекомендации по рациональному решению задачи:

1) сформируйте в столбце A, начиная с ячейки А3, список участников; чтобы избежать повторного ввода, скопируйте его и вставьте в ячейку B2 с помощью режима Специальная вставка " Транспонировать;

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

3) заполните часть результатов, чтобы можно было контролировать правильность вводимых формул;

4) в свободном столбце справа (столбец J) вычислите сумму элементов строки (это очки, набранные игроком при игре белыми фигурами);

5) в свободной строке снизу вычислите очки, набранные при игре черными, формула для первого участника (столбец B) будет такой:
=СЧЁТ(B$3: B$10)-СУММ(B$3: B$10)
(из общего числа сыгранных партий вычитаем потерянные очки);

6) растяните формулу по строке;

7) для того, чтобы набранные очки было удобно суммировать, скопируйте нижнюю строку и вставьте в ячейку K3 с помощью режима Специальная вставка " Транспонировать;

8) вычислите общее количество очков каждого участника (столбец L) просуммировав столбцы J и K, после этого вспомогательные столбцы можно скрыть;

9) формула для выявления лидера будет такой:
=ЕСЛИ(L3=МАКС(L$3: L$10); ”лидер”; ” ”)
(знак $ нужен, чтобы при растягивании формулы по столбцу диапазон не сместился);

10) и, наконец, скопируйте первый и последний столбцы в отдельную таблицу и отсортируйте по убыванию очков.

2.75. Смоделировать прогиб стальной балки.

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

где Е=2, 1•10¹¹ Па – модуль упругости,
I=19, 1•10^-6 м⁴ – момент инерции,
L=3, 6 м – длина балки,

P=30000 Н – нагрузка,
а=1 м – расстояние до точки приложения нагрузки.

Порядок выполнения задания:

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

2) рассчитайте по формуле прогиб в центре балки (при x=1, 8 м), результат выразите в миллиметрах, для этого его нужно умножить на 1000 (количество мм в 1 м);

3) постройте таблицу прогибов балки от воздействия различных значений нагрузок от 15000 Н до 40000 Н с шагом в 5000 Н в произвольном сечении х от 0 до L с шагом 0, 3 м;

4) оформите таблицу, как показано в образце; с помощью встроенной справки выясните, как создать пользовательский числовой формат, и создайте форматы с указанием единиц измерения «м» и «Н»;

5) постройте изображение изогнутой балки по таблице прогибов при нагрузке 30000 Н/м: скопируйте в отдельную область нужные столбцы, выберите точечную диаграмму, установите обратный порядок оси y, оформите диаграмму, как показано в образце, используя вкладку «Макет»;

6) рассчитайте, какова должна быть нагрузка P, чтобы прогиб в центре балки составил 2, 5 см, используйте режим «Подбор параметра» (вкладка «Данные» → Анализ «что если» → Подбор параметра);

После нажатия кнопки ОК расчет будет выполнен, и в указанную ячейку будет выведено искомое значение P.

7) определите с помощью подбора параметра, на каком расстоянии a должна быть приложена нагрузка 20000 H, чтобы прогиб балки в центре составил 2 см. (Ответ: a=0, 247872 м т.е. около 25 см).