Перемещение и копирование формул
После того как формула введена в ячейку, ее можно перенести, скопировать или распространить на блок ячеек.
При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, ссылки изменяются, но ячейка, где раньше находилась формула, остается без изменения. Формулу можно распространить на блок ячеек.
При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы $. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ $.
Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы изменится только номер строки “7”. Запись D$7 означает, что при перемещении будет изменяться только символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ $.
Функции категорий дата и время, текстовые
Возможность работы со строками текста и датами обеспечивают функции категорий Текстовые и Дата и Время. С их помощью можно заменять отдельные символы, искать текст с учётом регистра, использованного при вводе символов, преобразовывать число в текст и наоборот, определять число рабочих дней на различных временных интервалах и т.д. В работе рассматриваются следующие функции:
|
|
|
СЦЕПИТЬ(текст1, текст2, текст3) объединяет несколько строк в одну;
ЛЕСИМВ(текст, количество символов) – возвращает указанное число первых символов текстовой строки;
СЕГОДНЯ() – возвращает текущую дату в числовом формате;
ГОД(дата в числовом формате) – возвращает год соответствующий;
МЕСЯЦ(дата в числовом формате) – возвращает месяц соответствующий аргументу;
ЧАС(время в числовом формате)– возвращает час согласно аргументу;
МИНУТЫ(время в числовом формате)– возвращает минуты соответствующие аргументу.
ВПР(искомое_значение; таблица; №_столбца; интервальный_просмотр) ищет искомое_значение в крайнем левом столбце таблицы и возвращает значение той же строки из столбца (№_столбца).
ГПР(искомое_значение; таблица; №_строки; интервальный_просмотр) ищет искомое_значение (может быть текстом) в верхней строке таблицы и возвращает значение ячейки в указанной строке того же столбца (№_строки).
Упражнение 6. Дана следующая информация:
| Фамилия | Имя | Отчество | Город | Улица | Дом |
Обработать данные, чтобы получилась следующая конструкция:
|
|
|
| Фамилия И.О., г.Город, ул.Улица, д.Дом |
Для выполнения задания следует:
1. В ячейки В2: G 2 ввести строку заголовка (рис.3.9).
2. Ячейки B 3: G 6 заполнить данными.
3. Формула для ячейки B 9 (копируется на диапазон B 10: B 12):
=СЦЕПИТЬ(B3;" ";ЛЕВСИМВ(C3);".";ЛЕВСИМВ(D3);".,г.";E3;",ул.";F3;",д.";G3)
Рис.3.9 Конструкция Фамилия И.О., г.Город, ул.Улица, д.Дом
4. Отформатировать данные рабочего листа согласно рис 3.9.
Упражнение 7. Определить для работающих сотрудников стаж работы. Для выполнения задания нужно:
1. Ввести данные для вычисления стажа работы по столбцам: Фамилия, Дата рождения, Начало работы (рис.4.10).
2. Ввести формулы для расчёта:
| Ячейка | Формула | Формат вывода | Описание |
| С4 | =СЕГОДНЯ()-В4+1 | ГГ | Возраст |
| Е4 | =ГОД(СЕГОДНЯ()-D4)-1900 +МЕСЯЦ(СЕГОДНЯ()-D4)/12 | #0,00 | Стаж работы |
3. Скопировать формулы в соответствующие ячейки диапазона таблицы.
4. Отформатировать данные и результаты расчёта как на Рис.3.10.
Рис.3.10 Определение стажа работы
Упражнение 8. Определить дневную нагрузку компьютерного класса: № класса, Начало занятий, Конец занятий, Отработанное время, Характеристика загруженности класса (менее 2 часов – практически пустой, от 2 до 6 часов – малая загрузка, от 6 до 16 – норма, свыше 16 часов – перегружен). Для выполнения задания необходимо:
|
|
|
1. Ввести данные для вычисления нагрузки по столбцам: № класса, Начало занятий, Конец занятий, Отработанное время, Характеристика загруженности класса (Рис.3.11).
2. Ввести формулы для расчёта нагрузки компьютерного класса:
| Ячейка | Формула | Формат вывода | Описание |
| D 4 | =ЧАС(C4-B4)+МИНУТЫ(C4-B4)/60 | Числовой | Отработ.время |
| Е4 | =ЕСЛИ(D4<2;"практически пустой";ЕСЛИ(И( D4>=2; D4<=6); "малая загрузка"; ЕСЛИ(И(D4>6; D4<=16);"норма";"перегружен"))) | Хар-ка загруженности |
3. Скопировать формулы в соответствующие ячейки диапазона таблицы.
4. Отформатировать данные и результаты расчёта (Рис.3.11).
Рис.3.11 Определение нагрузки компьютерного класса
Блоки ячеек
Блок – это любая непрерывная прямоугольная область ячеек таблицы, которая задается указанием ее начальной (верхней левой) и конечной (нижней правой) ячеек, разделенных двоеточием. Например, A1:C3 – прямоугольный блок ячеек; A1:F1 – блок ячеек строки 1-й строки таблицы, A1:A5 – блок ячеек столбца A таблицы. Быстрое маркирование блока ячеек, содержащихся в одном столбце таблицы, выполняется щелчком мыши по заголовку столбца. Одновременно выделить несколько строк можно щелчком левой кнопки мыши по заголовкам строк и удерживая клавиши Ctrlили Shift. Маркировка с помощью клавиатуры производится нажатием клавиши F8 вначале а затем в конце блока.
|
|
|
Упражнение 9. Сформировать ведомость заработной платы, в которой надбавка за стаж определяется исходя из соответствующих надбавок за количество отработанных лет. Для решения данной задачи:
1. Сформировать ведомость, как показано на Рис.3.12.
2. В ячейку D 2 ввести формулу: =C2*ВПР(B2;$A$13:$B$17;2), где: В2 – искомое значение (стаж сотрудника), которое необходимо найти в таблице диапазоном $A$13:$B$17; Индекс 2 – номер столбца таблицы, из которого будет возвращено значение (процент надбавки); С2 – оклад сотрудника (Рис.3.12).
Рис.3.12 Исходные данные для ведомости зарплаты
3. В ячейку E 2 записать формулу =C2+D2.
4. Скопировать формулы в соответствующие диапазоны.
5. Отформатировать таблицу как на Рис.3.13.
Рис.3.13 Ведомость заработной платы
Дата добавления: 2020-01-07; просмотров: 320; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!