Простейшие манипуляции
3.1.1. Выделите на экране столбцы от А до L. Установите масштаб окна Excel так, чтобы на экране помещались эти столбцы (команда Вид ® Масштаб ® По выделению).
3.1.2. Введите на Лист1 информацию, указанную на рис. 3.1.1. Установите в ячейке В2 следующий формат: размер шрифта – 26 пт., цвет шрифта – красный, цвет заливки – желтый, выравнивание по центру, разрешен перенос по словам в пределах ячейки (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание). Скопируйте этот формат (но не содержание ячейки) на блок С3:Е4 (команды Правка ® Копировать, Правка ® Специальная вставка). Разместите в ячейках D4 и Е4 пятые степени чисел из D3 и Е3 соответственно.
A | B | C | D | E | F | |
Формат | ||||||
Число | ||||||
Пятая степень числа | ||||||
Рис. 3.1.1
3.1.3. Присвойте ячейкам А5 и F3 (см. рис. 3.1.1) имена соответственно Делимое и Делитель (команда Вставка ® Имя… ® Присвоить…). Введите в ячейку В5 формулу: =Делимое/Делитель. Укажите ячейки, влияющие на значение в В5 (команда Сервис ® Зависимости… ® Влияющие ячейки). Введите делитель, при котором в В5 результатом будет 50.
3.1.4. Скопируйте блок А2:Е5 (см. рис. 3.1.1) на Лист2, начиная с ячейки Y12345 (для быстрого перехода в удаленную ячейку следует после команды Правка ® Копировать ввести в адресное поле адрес нужной ячейки и нажать <Enter>).
3.1.5.Введите информацию с рис. 3.1.2. на Лист1. Отформатируйте таблицу: А9:D9 – жирный шрифт, выравнивание по центру; В10:В16 и D10:D16 – денежный формат; В10:D16 – выравнивание по центру; D10:D16 – красный шрифт, разлиновка – как на рис. 3.1.2 (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание, Число, Границы). Присвойте имена ячейкам F10 и G10 (команда Вставка ® Имя… ® Создать…) и блокам В10:В15 и С10:С15 (команда Вставка ® Имя… ® Присвоить…). Заполните столбец Выплата по формуле:
="Премия" * "Коэффициент премии" –
"Ошибки" * "Штраф за ошибку"
Введите формулы итогов в ячейки В16 и D16 (кнопка Автосумма или команда Вставка ® Функция…).
A | B | C | D | E | F | G | H | |
Ведомость поощрений | ||||||||
ФИО | Премия | Ошибки | Выплата | Коэффициент премии | Штраф за ошибку | |||
Булочкин | 0,9 | |||||||
Плюшкин | ||||||||
Пирожков | ||||||||
Пиццерин | ||||||||
Кулебяков | ||||||||
Расстегай | ||||||||
Итого: |
Рис. 3.1.2
3.1.6. Присвойте листам, на которых выполнялись задания 3.1.1–3.1.5, названия Формат и Копия, свободному листу Вашей рабочей книги – имя Сложение.
3.1.7. Введите на лист Сложение данные, указанные на рис. 3.1.3. Разместите в В10 их сумму, используя только заполненные числами ячейки (кнопка Автосумма или команда Вставка ® Функция…, выделение группы блоков через <Ctrl + буксировка).
A | B | C | D | E | F | G | H | I | |
Ответ: |
Рис. 3.1.3.
3.1.8. Разлинуйте таблицу в соответствии с рис. 3.1.4 (команда Формат ® Ячейки…, вкладка Границы). Заполните таблицу сложения с масштабом в соответствии с формулой: ((Х+ Y) *Масштаб) (пользуйтесь частичным и полным закреплением адресов). Масштабный коэффициент – в ячейке В19. Сосчитайтесумму диагональных элементов таблицы.
A | B | C | D | E | F | G | H | |
Х Y | 1,2 | 1,4 | 1,6 | 1,8 | ||||
Итого сумма диагональных элементов: | ||||||||
Масштаб: | 4,8 |
Рис. 3.1.4
3.1.9. Повторите задание 3.1.8, присвоив имена Х и Y блокам аргументов (через адресное поле или командой Вставка ® Имя…) и используя их в формулах вместо частичного закрепления адресов. Новую таблицу расположите, начиная с ячейки А21. Сравните вид формул и результаты в обоих заданиях.
3.1.10. Составьте на новом листе две таблицы умножения по аналогии с рис. 3.1.4. Заполните одну из них, используя частичное закрепление адресов, а другую – имена блоков аргументов (для аргументов следует использовать имена, не совпадающие с теми, которые были использованы в задании 3.1.9). Сосчитайте сумму диагональных элементов в полученной таблице.
3.1.11. Присвойте чистому листу имя Заполнение. Введите в ячейки А1 и В1 число 2, в ячейки А2 и В2 – число 2,5. Заполните блок А1:А10 по арифметической, а блок В1:В10 по геометрической прогрессии (следует выделить нужный блок и далее воспользоваться командой Правка ® Заполнить ® Прогрессия…). Выделите значения, которые больше пяти, голубой заливкой (команда Формат ® Условное форматирование…).
3.1.12. Петров работает в марте по четным дням, Иванов – по нечетным, Сидоров – каждый день. Составьте и красиво оформите календарный график их работы (автозаполнение протяжкой). Выделите условным форматированием рабочие дни в период школьных каникул (с 24 марта).
3.1.13. Первоначальный запас рыбы в озере 1200 тонн. Естественный ежегодный прирост составляет 15 %. Ежегодный план отлова – 300 т. Порог, после которого запас рыбы невосстановим, составляет 400 т. Рассчитайте запас рыбы в озере на ближайшие 15 лет. С помощью условного форматирования выделите красной заливкой годы, в которые запас рыбы окажется меньше критического порога. Для лет, в которые запас рыбы полностью иссякнет (отрицательные числа), дополнительно закажите красный шрифт. Составьте вспомогательную таблицу, отражающую естественное восстановление рыбного запаса при условии запрета на отлов, начиная с года, предшествующего критическому. С помощью условного форматирования отметьте зеленой заливкой годы, в которые запас рыбы полностью восстановится.
3.1.14. Перед началом строительства была рассчитана стартовая цена квартиры в долларах и собраны начальные взносы пайщиков. Впоследствии оказалось, что для завершения стройки не хватает 0,5 млн рублей. Рассчитайте долги пайщиков с учетом этого факта. Расположите и отформатируйте информацию в соответствии с рис. 3.1.5, введите недостающие константы и формулы.
A | B | C | D | E | F | G | |
Начальная цена квартиры | № квартиры | Начальный взнос | Долг | № квартиры | Начальный взнос | Долг | |
$30 400 | $12 258 | $6 000 | |||||
$4 000 | $6 200 | ||||||
Курс доллара | $15 640 | $9 500 | |||||
$8 520 | $9 800 | ||||||
$10 000 | $30 400 | ||||||
Общий долг | $17 000 | $30 400 | |||||
$1 742 | $25 000 | ||||||
$18 910 | $12 500 | ||||||
Новая цена квартиры | $14 980 | $20 500 | |||||
$20 000 | $9 600 | ||||||
Итого по дому: |
Рис. 3.1.5
3.1.15. Оформите ведомость по закупке товаров, включающую 8–10 наименований (рис. 3.1.6). Введите свои значения в графы 2 и 3, расчетные формулы – в графу 4 и строки "Итого" и "Разница". Добавьте с помощью форматирования указатели единиц измерения (шт., пачки, рубли и т. п.). Подберите количество каждого товара так, чтобы итоговая сумма не превышала заданной, но была максимально близка к ней.
A | B | C | D | |
Ведомость закупки товаров | Количество | Цена за единицу товара | Стоимость | |
Карандаши | ||||
Авторучки | ||||
Бумага писчая | ||||
… | ||||
Итого | ||||
Заданная сумма | 50 000 р | |||
Разница |
Рис. 3.1.6
3.1.16. Заказчик оценил всю работу в 10000 долларов и выдал аванс в размере 4000 долларов. Этот аванс был распределен между работниками произвольным образом (кто сколько попросил). Требуется по окончании работы распределить оставшиеся 6000 долларов с учетом ранее выданного аванса, коэффициента трудового участия (КТУ) и коэффициента профессионального класса.
Подсказка
Расположите и отформатируйте информацию в соответствии с рис. 3.1.7, введите недостающие формулы, смысл которых описан ниже:
"Заработано" = "Всего заработано" * ("КТУ работника" / "Итого КТУ") * "Коэффициент за класс"
"Корректировка" = "Заработано" * ("Всего заработано" / "Итого заработано")
Формулы в строке "Итого" и столбце "Выдать остаток" составьте сами.
A | B | C | D | E | F | G | H | I | |
Всего заработано | $10 000 | Аванс | $4 000 | Остаток | $6 000 | ||||
Распределение зарплаты | |||||||||
ФИО | Класс | Коэффициент за класс | КТУ | Получен аванс | Заработано | Корректировка | Выдать остаток | ||
Петр | 1,4 | 1 000р. | |||||||
Иван | 1,2 | 1,5 | 800р. | ||||||
Олег | 1,2 | 1,1 | 1 200р. | ||||||
Вера | 1 000р. | ||||||||
Итого: | |||||||||
Рис. 3.1.7
3.1.17. По образцу рис. 3.1.8 составьте расчетную схему для определения ежемесячных доходов по вкладам в банке при простом и сложном проценте.
Формулы для расчетов (S0 – начальная сумма вклада, a – годовой процент): через i месяцев сумма вклада составит при простом проценте Si = Si –1 + (a / 12)S0, при сложном Si = Si –1 + (a / 12)Si –1.
A | B | C | D | E | F | G | H | I | |
Начальная сумма вклада, р. | |||||||||
Годовой процент простой | 10% | ||||||||
Годовой процент сложный | 10% | ||||||||
Рост доходов | |||||||||
Месяцы после открытия вклада | |||||||||
Сумма вклада при простом проценте | |||||||||
Прирост (простой процент) | |||||||||
Сумма вклада при сложном проценте | |||||||||
Прирост (сложный процент) | |||||||||
Разность сумм при сложном и простом проценте | |||||||||
Рис. 3.1.8
3.1.18.На рис. 3.1.9 представлены сведения, необходимые для начисления зарплаты сотрудникам фирмы. С помощью функции ВПР() перенесите в блок Е3:Е7 тарифы, соответствующие разряду работников, а также заполните графу "Начислено". Тарифы занесены в справочную таблицу (блок А2:В6).
Подсказка
Функция ВПР() находится в Мастере функций в категории Ссылки и массивы. Ее следует ввести в ячейку Е3 со следующими значения аргументов:
· Искомое_значение – D3.
· Табл_массив (где искать строку, начинающуюся со значения из D3) – $А$3:$В$6.
· Номер_столбца (в каком столбце найденной строки искать нужное данное) – 2.
· Диапазон_просмотра – 0 (в блоке $А$3:$В$6 ищется строка, в которой первое значение точно совпадает с D3).
Далее формула копируется на остальные ячейки этой графы.
A | B | C | D | E | F | G | ||
Тарифы | Ведомость зарплаты | |||||||
Разряд | Тариф (р./ч) | ФИО | Разряд | Тариф (р./ч) | Выработка (ч) | Начислено (р.) | ||
Боровиков | ||||||||
Рыжик | ||||||||
Сыроежкин | ||||||||
Сморчков | ||||||||
Опенкин | ||||||||
Рис. 3.1.9
3.1.19. На рис. 3.1.10 изображен рабочий график сотрудников фирмы. Требуется найти в нем людей, которые работали в интересующие нас дни.
A | B | C | D | E | F | G | H | ||
Искомые даты: | 06 окт 03 | 11 окт 03 | 15 окт 03 | 17 окт 03 | |||||
График работ | |||||||||
ФИО | Рабочие дни | ||||||||
Плюшкин | 10 окт 03 | 11 окт 03 | 12 окт 03 | 13 окт 03 | 20 окт 03 | 21 окт 03 | |||
Пирожков | 06 окт 03 | 09 окт 03 | 10 окт 03 | 11 окт 03 | 15 окт 03 | 17 окт 03 | |||
Пряничков | 10 окт 03 | 11 окт 03 | 12 окт 03 | 13 окт 03 | 14 окт 03 | 18 окт 03 | |||
Конфеткин | 05 окт 03 | 06 окт 03 | 07 окт 03 | 11 окт 03 | 12 окт 03 | 17 окт 03 | |||
Рис. 3.1.10
Подсказка
В этой задаче удобно использовать команду Формат ® Условное форматирование… для выделения интересующих нас дней. При задании образца для форматирования в параметрах этой команды следует использовать функцию ГПР().
1. Установите курсор на ячейку В4 и вызовите команду Формат ® Условное форматирование...
2. В первой строке окна команды три текстовых поля. Установите в них соответственно:
Формула; Равно; =ГПР(B4;$D$1:$G$1;1;0)
Формулу наберите вручную, так как Мастер функций в этом окне недоступен. Аргумент В4 не должен быть закреплен. Смысл аргументов функции ГПР():
· Искомое_значение – В4 (какую дату ищем).
· Таблица –$D$1:$G$1 (таблица, в которой ведется поиск столбца, начинающегося с того же значения, что и в В4).
· Номер_строки – 1 (из какой строки найденного столбца следует брать значение функции ГПР()).
· Диапазон_просмотра – 0 (определяется тип поиска: 0 – в первой строке ищется точное совпадение с В4, 1 – в первой строке ищется ближайшее значение, которое меньше значения в В4 или равно ему).
Формат ячеек, удовлетворяющих этому условию, задайте сами.
3. С помощью команд Правка ® Копировать и Правка ® Специальная вставка… скопируйте формат из В4 на всю область Графика работ.
После того, как условный формат будет создан, поменяйте даты в списке искомых дат (их можно ввести меньше первоначального количества). Посмотрите, как будет меняться вид рабочего графика.
Дата добавления: 2016-06-22; просмотров: 1370;