Смешанные адреса. Стиль адресация будет меняться по циклу
Придется повторить(repeat)предыдущие шаги 2.3. и 2.4. еще раз по-другому.
1. В ячейке D4– набрать формулу: =С4/С11;
2. Нажать на клавиатуре F4(адрес станет $C$11);
3. Окончательная формула: =С4/$C$11; Ctrl + Enter
4. Скопировать формулу на весь диапазон.
3. Точно также введем формулу в колонку F4:
3.1. Выделить F4:F11;
3.2. F4 - = E4/E11;
3.3. Нажать F4, Ctrl + Enter;
3.4. Формула скопирована на весь диапазон.
4. При необходимости отформатируйте колонки D и E с точностью до 2-
х знаков после запятой при помощи пиктограмм (увеличить разрядность или уменьшить разрядность):
4.1. Выделить диапазон D4:D11 (E4:E11);
4.2.Щелчок по пиктограммам изменения разрядности.
5.Расчет отклонений
5.1.Встать в ячейку G4.
5.2.Вызвать мастер функций при помощи пиктограммы 
5.3.Выбрать категорию: логическиеи в нем функцию ЕСЛИ:
5.4.Нажать на клавишу «OK».
5.5. В появившемся окне ввести формулу:
В строке «Логическое_выражение»– условие выбора: Е4>С4;
В строке «Значение_если_истина»- E4-C4,выводится разница между планом и фактом поставки;
7Комбинация клавиш Ctrl + Enter используется для копирования формулы на весь выделенный диапазон. Иногда проще заранее выделить блок и размножить через клавиатуру, чем «тащить» формулу непослушной мышью через всю таблицу
В строке «Значение_если_ложь»- знак «минус» или просто пробел:
если ложь, то в данной ячейке ничего не выводим.
5.6. Нажать на клавишу «OK».Примерный вид экрана на рис 3.4.
5.7. Размножить формулу.
Рис. 3.4.
6. Аналогично рассчитать отклонение в колонке Н(знак >заменить на <).
7. Отсортировать таблицу по возрастанию значений колонки G,затем
по второму ключу-убыванию колонки Н.
8. Отрицательные значения колонки Нвыделить красным цветом через
пиктограмму - цвет шрифта.
После выполнения всех пунктов задания документ на Рис. 3.3. будет иметь следующий вид8:
Самостоятельная работа № 2.
Рис. 3.3.(итоговый)
Создать документ, изображенный на Рис. 3.5 (расчетные (выходные)
колонки –суммы и отклонения, остальные – входные, заполняете сами).
8 Для внимательных: если все правильно, то отрицательные значения в Вашей таблице имеют другой порядок, чем на «старом» рис. 3.3, приведенном без сортировки по
второму уровню (ключу) колонки Н
Самостоятельная работа № 3.
Рис. 3.5.
Используя понятие абсолютного адреса (в идеале смешанного, тогда формула будет введена только один раз в ячейке В3 и размножена на всю таблицу), создать таблицу умножения в виде таблицы Пифагора (Рис. 3.6.). Для форматирования ширины столбцов воспользоваться ФорматÞ ÞСтолбецÞ ШиринаÞ 3 или Автоподбор.
Самостоятельная работа № 4.
Рис. 3.6.
1. Торговый агент получает процент от суммы совершенной сделки.
Если объем сделки до 3000, то 2%; если объем до 10000, то 3%; если выше
10000, то 5%. Рассчитать сумму вознаграждения.
2. На предприятии персонал работает по графику: 12-часовая дневная смена, 12-часовая ночная смена, затем двое суток отдыха. Составить скользящий график для 8 человек на март месяц:
- для первого – день, ночь, отдых, отдых, день …..;
- для второго - ночь, отдых, отдых, день, …. и т.д.
(Просто методами автозаполнения создать таблицу- график без вычислений)
3.3. Работа с листами, автоформат
Очень часто в документах могут использоваться данные, которые находятся на других листах. Эти ссылки на данные в других листах вводятся одинаково: = Наименование листа! Адрес ячейки;
Например: =Лист2!В3. Означает ссылку на ячейку В3 листа 2.
Задание 3. Используя данные с листа Х (Рис. 3.7), получить таблицу «Справка о возрасте», изображенную на Рис. 3.8.
Постановка задачи.
1. ЛистХ переименовать как «Справочник сотрудников»и создать документ, изображенный на Рис. 3.7.
Рис. 3.7.
2. ЛистХ+1 переименовать как «Возраст»и ввести только первые 2 строки, в остальные строки - только формулы и ссылки следующим образом:
3. Столбец Bлиста «Возраст» (Фамилия И.О.) формируется при помощи сцепления (присоединения) к фамилии первых букв с имени
и отчества при помощи встроенной функции Левсимвданными из трех столбцов B, C, Dлиста «Справочник сотрудников» (Фамилия
Имя Отчество).
4. Для вычисления Возраставоспользуйтесь формулуй «Текущая дата
-дата рождения».В качестве текущей даты используется функция
«Сегодня». Выводить только число полных лет.
Порядок действий.
1. Переименовать ЛистХ (подвести курсор на Лист, нажать
правую кнопку мыши и выбрать пункт Переименовать, ввести название листа «Справочник сотрудников» и нажать Enter);
2. Ввести данные в ячейки, отцентрировать:
· Порядковый номер ввести при помощи автозаполнения
(ввести 1 и 2, выделить обе ячейки и «потянуть» мышью);
· При вводе фамилий, имен, отчеств можно воспользоваться (после ввода 3-4 строк) Контекстным менюÞ выбор из списка.
3.Перейти щелчком на ЛистХ+1(переименовать лист как
Возраст);
4.В ячейку А1ввести «Справка о возрасте», выровнять по центру, выделив диапазон А:D;
5.В ячейки А2, В2, С2, D2ввести «№ п/п, Фамилия И.О., Дата рождения, Возраст»;
6. В ячейку А3ввести =(равно), перейти на «Справочник сотрудников», щелкнуть по ячейке А3и в строке формул
появится запись-формула ='Справочник сотрудников'!A3,
нажать Enter;
7. Выделить ячейку А3листа Возраст и «потянуть» мышью вниз.
Колонка сформирована;
8.В ячейку В3ввести =(равно), перейти на «Справочник сотрудников», щелкнуть по ячейке В3, в строке формул появится запись-формула ='Справочник сотрудников'!B3;
9. Щелчок по строке формул, набрать (& - сцепить) &
“пробел9”&;
1 Ввод формул можно и нужно прослеживать через строку формул, в конце -
концов должна собраться общая длинная формула, приведенная чуть ниже.
10. Нажать (для извлечения первого символа из имени), категория: Текстовые, выбрать функцию ЛЕВСИМВ(функция возвращает заданное число первых левых символов текста), нажать OK;
11. В окне ЛЕВСИМВв строке Текстщелкнуть по листу Справочник сотрудников, затем по ячейке С3; перейти на строку Число_литери ввести 1, нажать на ОКи в строке
формул появиться выражение: ='Справочник
сотрудников'!B3&" "&ЛЕВСИМВ('Справочник сотрудников'!C3;1)(В Строке формул постепенно выстраивается приведенное чуть ниже выражение)
12. Курсор на строке формул, ввести: &«.»&,повторить п.11, вместо ячейки С3выбрать - D3, курсор на строку формул, набрать & «.»,нажать Enter. И наконец-то в строке формул появится формула:
9Символьные выражения всегда заключены в двойные кавычки, а пробел такой же нормальный символ, как и все другие. Здесь он нужен для разделения Фамилии и Имени
='Справочник сотрудников'!B3 &" "&
ЛЕВСИМВ ('Справочник сотрудников'!C3;1)&"." &
ЛЕВСИМВ('Справочник сотрудников'!D3;1)&"."
13. Скопировать формулу вниз на всю колонку.
14. Встать на ячейку С3, набрать =, щелчок по листу Справочник сотрудников, в нем Е3, нажать Enter, при изменении формата ячейки установить правильный Тип даты (ДД/ММ/ГГ) через ФорматJЯчейки, растянуть формулу вниз. Колонка С сформирована.
15. Встать на ячейку на D3, нажать 
,выбрать категорию - Дата и