Смешанные адреса. Стиль адресация будет меняться по циклу

Придется повторить(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, нажать ,выбрать категорию - Дата и