Закрепление первых строк и столбцов

Следующим инструментом, который упростит работу с большими объемами данных на листе является возможность закреплять определенные строки или столбцы диапазонов.

Такая возможность пригодится, когда нужно держать на виду заголовки таблиц с которыми ведется работа, а количество записей превышают возможности экрана.

Команда закрепления областей находится в группе «Окно» вкладки «Вид».

Закрепление областей в Excel

Можно закрепить первую строку листа, закрепить первый столбец листа эти возможности пригодятся если таблица не помещается только по вертикали, либо горизонтали и только, если заголовок размещен в одной строке/столбце.

Более вероятна ситуация, когда нужно будет закрепить несколько первых строк и/или столбцов. Для этого предусмотрена команда «Закрепить области», чтобы правильно воспользоваться данной командой нужно установить курсор выделения ячейки на ту позицию, выше которой нужно закрепить строки и левее которой нужно закрепить столбцы.

Например, выполнение команды «Закрепить области» с установленным курсором в ячейке B4, закрепить первые 3 строки и столбец «A». Закрепленные области отделяются слегка заметной серой линией на листе.

Закрепятся области выше и левее выделенной ячейки

 

Если на листе уже есть закрепленные области, то команда «Закрепить области» меняет свое название на «Снять закрепление областей» и для создание новой закрепленной области следует предварительно убрать предыдущее закрепление.

На листе одновременно может быть закреплена только одна область

Режимы просмотра книги

По умолчанию книга в Excel редактируется в режиме «Обычный», однако в табличном редакторе предусмотрено еще два режима отображения: «Страничный режим», «Разметка страницы». Переключить режимы представления можно с помощью ленты интерфейса: вкладка «Вид» группа «Режимы просмотра книги», либо справа внизу книги Excel рядом с элементами изменения масштаба есть три управляющих кнопки.

Переключить режимы просмотра можно с информационной панели

Страничный режим

 

Страничный режим позволяет увидеть сколько реальных листов займет рабочий лист Excel при печати

В этом режиме синими областями выделяются области листа Excel, которые будут соответствовать реальным страницам при печати документа (границы можно передвигать с помощью мышки).

Разметка страницы

С помощью представления разметки страницы можно настроить границы страницы. Этот режим максимально похож на стандартный режим редактирования в Word.

Данный режим показывает, как будет выглядеть распечатанный текст на реальной странице после печати, в отличие от предыдущего режима можно посмотреть и отрегулировать границы страницы. Режим разметки очень похож на режим разметки в текстовом процессоре MS Word.

 

Задание для самостоятельной работы

1. Открыть документ из предыдущего задания «Задание 3.3 выполнено».
2. На листе «Отчеты по дням» вызвать контекстное меню на любой ячейке таблицы, выбрать пункт: Таблица/ Преобразовать в диапазон.

Пояснение: таблицы и преимущества, которые они дают, будут рассматриваться позже в рамках данного курса.

1. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Закрепить верхнюю строку». Прокрутить лист вниз и убедиться, что первая строка всегда находится на экране.
2. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Закрепить первый столбец». Прокрутить лист вправо и убедиться, что первый столбце всегда на экране. Прокрутить лист вниз и убедиться, что первая строка уже не зафиксирована.
3. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Снять закрепление областей». Установить курсор в ячейку C2 и выбрать команду: «Закрепить области/ Закрепить области».
4. Убедиться, что при прокручивании листа вниз зафиксированной остается первая строка, а при прокручивании вправо – первые два столбца.
5. Сохранить документ под именем «Задание 3.4 выполнено».

 

 

Именно возможность производить различного рода вычисления и снискали мировую популярность табличному процессору Excel из пакета Microsoft Office.

До этого занятия мы фактически не использовали Excel для проведения расчетов, начиная с данного занятия попытаемся если не полностью раскрыть, то продемонстрировать тот потенциал, который заложен в эту программу.

Основы построения формул

Если в начале ячейки поставить знак «=», то Excel начинает воспринимать ячейку как формулу.

В качестве простейшего примера формулы можно представить простую операцию суммирования двух чисел, вычитания, умножения и деления. В Excel работают стандартные математические операции и последовательность их применения.

Простейшая формула в Excel

По умолчанию, в ячейке, содержащей формулу, для пользователя отображается результат ее вычисления, если нужно посмотреть сами формулы, сделать этом можно командой отображения формул «Показать формулы», группа «Зависимости формул», вкладка «Формулы», либо нажать горячее сочетание Ctrl+` (клавиша тильда стоит в начале цифрового ряда на клавиатуре).

Горячее сочетание

Ctrl+` переключает режимы отображения данные/ формулы в Excel

Формула представляет собой уравнение, которое производит вычисления такие как: сложение, вычитание, умножение, деление. В Excel, в качестве значений формулы может быть число, адрес ячейки, дата, текст, булево значение (правда или ложь). чаще всего это либо число, либо адрес ячейки.

Формулы содержат два типа элементов: операнды и операторы вычислений. Операнды – это значения, которые используются в вычислениях. Операнд может быть константой, например, число «5», или переменной, т.е. ссылкой на ячейку, диапазон или другую формулу, например, ссылка на ячейку, которая содержит число «5».

Константа – это значение, которое вводится непосредственно в формулу (число, текст, дата).

Переменная – это символьное обозначение значения, которое находится вне формулы.

Использование ссылки и константы в формуле Excel

Оператор вычисления определяет само вычисление, для того, чтобы Excel начал производить вычисление в формуле в начале надо поставить знак «=». Excel поддерживает работу со следующими типами арифметических операторов:

Оператор	Результат
+	Сложение
—	Вычитание
*	Умножение
/	Деление
%	Процент (сотая часть числа)
^	Возведение в степень

Во время введения формулы, операторы и значения отображаются и в ячейке, и в самой формуле, когда ввод закончен в строке формул будет формула, тогда как в ячейке – результат ее работы.

Порядок вычисления формулы

Когда в формуле применяется несколько вычислительных операторов, то порядок их вычисления подчинен общим математическим правилам. Следующий порядок применяется при расчете формулы (записано в порядке убывания приоритета вычисления):

1. Отрицательные значения (-);
2. Проценты (%);
3. Возведение в степень (^);
4. Умножение (*) и деление (/);
5. Сумма (+) и разность (-).

Если рядом находятся два или более вычислительных операторов, которые находятся на одном уровне, то порядок их вычисления идет слева на право. Например, «=5+5+3-2», хотя здесь этот порядок имеет значение сугубо для логики произведения вычисления, т.к. отнимите вы число «2» в конце вычисления или в начале на результат не повлияет. Если возникает необходимость повысить приоритет вычисления отдельной части формулы, то, как и в математике, нужно эту часть заключить в круглые скобки.

Несколько примеров работы вычислений в Excel:

Примеры простых формул в Excel

Горячее сочетание

Если во время ввода формулы вы передумали, то просто нажмите «Esc» и значение в ячейке не будет изменено. Если формулу уже изменили (завершили ввод с помощью Enter), то вернуть старое значение можно с помощью команды «Отменить» на панели быстрого доступа или сочетания Ctrl+Z.

Для того, чтобы отредактировать существующую формулу, необходимо установить курсор в ячейку с формулой и нажать клавишу «F2», либо кликнуть в строку формул, либо дважды кликнуть по целевой ячейке.

 

 

Задание для самостоятельной работы

На месте преступления оставлен след, предположительно преступника, 43й размер, ботинок мужской. Не основании этой информации, а также учитывая тот факт, свидетели отметили, как парень лет 27 покидал место преступления, нужно рассчитать некоторые антропометрические данные человека: Рост, длину шага, ширину ступни, длину пятки, длину головы, плеча, предплечья, бедра, голени и идеальную массу.

Формулы для расчета показателей:

• Рост (см) = (длина ступни – 15) *100/15,8+100
• Длина шага (см) = длина ступни * 3
• Ширина ступни (см) = Рост / 18
• Длина пятки (см) = Рост / 27
• Голова (см) = Рост / 8
• Плечо (см) = (Рост – 73,6) / 2,97
• Предплечье (см) = (Рост – 80,4) / 3,65
• Бедро (см) = (Рост – 69,1) / 2,24
• Голень (см) = (Рост – 72,6) / 2,53
• Идеальная масса (кг) = (Рост * 3 / 10 + 450 + Возраст) * 0,25 — 55

Инструкции по произведению вычислений:

1. Открыть книгу «Занятие 4.1 старт».
2. Найти соответствие длины ступни размеру обуви в сети Интернет.
3. Рассчитать показатели, на основе найденного значения длины ступни.
4. В формулах использовать ссылки на рассчитанные ранее значения, например, если для расчета плеча используется рост, то нужно использовать ссылку на рассчитанное значение роста.
5. Сохранить книгу под именем «Занятие 4.1 выполнено».

 

 

Ранее упоминалось, что одним из значений, которые используются в формулах, являются переменные. Переменные используются намного чаще нежели константы, более того, рекомендуется все константы заменять переменными (подробнее об этом поговорим в разрезе рассмотрения абсолютных ссылок).

Итак, переменные в Excel представлены ссылками на другие ячейки. Рассмотрим два примера, которые фактически идентичны.

Ссылки на ячейки в формуле Excel

Каждая ячейка находится на пересечении строк и столбцов и имеет соответствующее обозначение:

• «E2» – столбец «E», вторая строка;
• «F2» – столбец «F», вторая строка;
• «G2» – столбец «F», вторая строка.

Преимущество такой записи заключается в том, что при изменении значений в ячейках, задействованных в формуле, результат вычисления формулы также изменится. Естественно формулы могут содержать и ссылки (переменные), и числа (константы) одновременно.

Относительные ссылки

В рассмотренном примере был рассмотрен самый простой тип ссылок – относительные ссылки, принцип его работы легко понять на примере пересчета дневного заработка в другою валюту.

Относительная ссылка в Excel

Простая формула пересчета актуального заработка в рубли вводится в ячейку C2, а потом, с помощью маркера автозаполнения растягивается на остальные дни, как можно заметить, для ячейки C3 в формуле используется заработок за 02.03.2016, хотя формула вводилась «=B2*71», т.е. при сдвиге на одну ячейку вниз, введенная относительная ссылка, также меняет свой адрес. То же самое происходит, если растянуть маркер автозаполнения в любую из сторон (влево, вправо, вверх или вниз), относительная ссылка всегда будет находится на одном расстоянии от ячейки с формулой, где она используется. В данном случае, это на одну ячейку влево, т.е., если потянуть маркер автозаполнения вправо, то в формуле будет использоваться столбец «C» (и число в зависимости от текущей строки).