- •Часть 2
- •Скрытие строк, столбцов, листов и книг
- •Режим скрытия формул
- •Защита листов, книг, данных. Защита книг и листов
- •Ввод паролей
- •Удаление защиты
- •Защита данных
- •Команда Подбор параметра
- •Лабораторная работа № 10 Тема: Адресация
- •Абсолютные, относительные и смешанные ссылки
- •Изменение типа ссылок
- •Ссылки на ячейки за пределами текущего рабочего листа
- •Ссылки на ячейки из других рабочих листов
- •Ссылки на ячейки из других рабочих книг
- •Лабораторная работа №11 Тема: Числовые форматы. Особенности форматирования Назначение и удаление форматов
- •Применение автоформата
- •Общий формат
- •Дробные форматы
- •Создание пользовательских числовых форматов
- •Форматирование положительных, отрицательных, нулевых и текстовых значений
- •Включение цвета в форматы
- •Использование условий в пользовательских форматах
- •Условное форматирование
- •Управление правилами условного форматирования
- •Лабораторная работа №12 Тема: Работа со строками и датами
- •Использование функций
- •Синтаксис функций
- •Функция пстр
- •Функции заменить и подставить
- •Функции даты и времени
- •Форматирование дат и времени
- •Ввод дат и времени
- •Арифметические операции с датами и временем
- •Функция дней360
- •Функция час
- •Изменение шкалы оси времени
- •Задание минимального и максимального значений
- •Изменение цены основных и промежуточных делений
- •Изменение единицы измерения
- •Лабораторная работа №13 Тема: Списки
- •Что можно делать со списком
- •Планирование списка
- •Ввод данных в список
- •Ввод данных вручную
- •Ввод данных с помощью формы ввода
- •Фильтрация списков
- •Автоматическая фильтрация
- •Автоматическая фильтрация по значениям в нескольких столбцах
- •Пользовательский автофильтр
- •Наложение условия по списку
- •Построение диаграммы по данным отфильтрованного списка
- •Расширенная фильтрация
- •Установка диапазона критериев
- •Множественный критерий отбора
- •Текстовые и числовые критерии
- •Вычисляемые критерии
- •Другие возможности расширенной фильтрации
- •Копирование отобранных строк
- •Отображение только уникальных строк
- •Сортировка списка
- •Простая сортировка
- •Более сложная сортировка
- •Правила сортировки в Excel
- •Особый порядок сортировки
- •Создание промежуточных итогов
- •Лабораторная работа №14 Темы: Сводные таблицы
- •Создание сводной таблицы
- •Добавление нового поля
- •Обновление сводной таблицы
- •Форматирование сводной таблицы
- •Использование сводных таблиц для консолидации данных
Лабораторная работа № 10 Тема: Адресация
Практически все формулы включают ссылки на ячейки или диапазоны ячеек. Эти ссылки позволяют работать с данными, содержащимися в этих ячейках и диапазонах, а не просто использовать фиксированные значения. Если формула имеет ссылку на ячейку А1, и вы изменили значение в этой ячейке, формула автоматически пересчитается в соответствии с новым значением ячейки А1. Если не использовать ссылок на ячейки, вам придется вручную редактировать формулы, изменяя значения, которые она использует в своих вычислениях. В формулах используются три типа ссылок на ячейки и диапазоны.
Абсолютные, относительные и смешанные ссылки
Абсолютные ссылки. Эти ссылки не меняются при копировании формул.
Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
Смешанные ссылки. В этих ссылках номер строки или столбца является абсолютным, а столбца (строки) – относительным.
При создании формул необходимо хорошо понимать различие между относительными и абсолютными ссылками. По умолчанию Excelсоздает в формулах относительные ссылки, за исключением ссылок на ячейки, находящиеся в других листах или рабочих книгах. Различие между разными типами ссылок проявляется при копировании формул.
На рис. 1 показаны результаты копирования формул с различными типами ссылок. При копировании формулы с относительными ссылками Excelне создает точной копии формулы – в копируемой формуле ссылки изменяются в соответствии с новым местоположением формулы.
Исходная формула содержит инструкцию о сложении значений двух ячеек, которые находятся на 15-16 строк ниже и на 1 столбец левее ячейки, в которой находится формула; результат сложения затем делится на значение ячейки с формулой, которая находится на 17 строк ниже и на 1 столбец левее ячейки. При копировании формулы копируется данная инструкция, а не фактическое содержимое ячейки. Как правило, это и требуется пользователю. Вряд ли вам нужно полностью копировать формулу, ведь в таком случае она будет давать те же результаты, что и первоначальная формула.
Однако бывают случаи, когда необходимо, чтобы адреса ячеек копировались полностью. Пример формулы, содержащей абсолютную ссылку, показан на рис. 1. В данном примере в ячейке В6 содержится формула, использующая абсолютные ссылки на все ячейки. Эта формула имеет вид:
=($А$14+$А$15)/$А$16
Обратите внимание, что в формуле перед буквами столбцов и перед номерами строк стоят знаки доллара. Это означает, что данные ссылки являются абсолютными. При копировании этой формулы в ячейку В7 будет получена следующая формула:
=($А$14+$А$15)/$А$16
В данном случае формула не изменилась, поскольку ссылки абсолютные.
В абсолютных ссылках используются два знака доллара: один перед буквой столбца и другой перед номером строки.
В смешанных ссылках только одна часть адреса абсолютная. На рис. 1 в диапазоне В10:В13 показано действие смешанных ссылок.
Рис.10.1
В каких случаях используется смешанная ссылка? Например, она необходима в ситуации, показанной на рис. 2. Этот лист будет содержать таблицу чисел, в каждой ячейке которой должно находиться значение, взятое из столбца А, умноженное на значение из строки 1. Формула в ячейке В2 выглядит следующим образом:
=В&1*$А2
Приведенная формула содержит две смешанные ссылки на ячейки. В ссылке В$1 координата строки является абсолютной, а координата столбца – относительной. В ссылке $А2, наоборот, координата строки является относительной, а координата столбца – абсолютной. Вы можете скопировать эту формулу в диапазон В2:G5, и в каждой ячейке будут содержаться правильные формулы. Например, формула в ячейкеG5 будет выглядеть следующим образом:
=G$1*$А5
Рис.10.2