Как закрепить строку в excel при прокрутке

Фиксация по столбцу и строке

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

Так, выражение «=$A1/E4» при копировании из E5 в D5 перевоплотится в «$A1/D4», что дозволит посчитать различные значения лишь с одним постоянным коэффициентом (курсом валют, процентом ставки). Но если юзер копирует формулу вдоль столбца, то оставаться без конфигураций будет лишь 1-ая часть ссылки: «А» — «A1», «A2», «A3» и т. д.

Очередной метод, как зафиксировать ячейку в Excel, подразумевает фиксацию лишь по строке. В этом случае символ $ в ссылке ставится перед вторым идентификатором — к примеру, A$1. В этом случае юзер может протянуть формулу, копируя ее по прямоугольной области — но изменяться будет лишь столбец. Так можно создать, установив в одной из строк проценты, которые будут различные для каждой группы. При копировании таковая процентная ставка меняется по столбцам.

Как закрепить строку в excel, столбец и область

Один из наиболее частых вопросов пользователей, которые начинают работу в Excel и особенно когда начинается работа с большими таблицами — это как закрепить строку в excel при прокрутке, как закрепить столбец и чем отличается закрепление области от строк и столбцов. Разработчики программы предложили пользователям несколько инструментов для облегчения работы. Эти инструменты позволяют зафиксировать некоторую часть ячеек по горизонтали, по вертикали или же даже в обоих направлениях.

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

В экселе можно закрепить строку можно двумя способами. При помощи инструмента «Закрепить верхнюю строку» и «Закрепить области».

Первый инструмент подходит для быстрого закрепления одной самой верхней строки и он ничем не отличается от инструмента «Закрепить области» при выделении первой строки. Поэтому можно всегда пользоваться только последним.

Чтобы закрепить какую либо строку и сделать ее неподвижной при прокрутке всего документа:

1. Выделите строку, выше которой необходимо закрепить. На изображении ниже для закрепления первой строки я выделил строку под номером 2. Как я говорил выше, для первой строки можно ничего не выделять и просто нажать на пункт «Закрепить верхнюю строку».

   Для закрепления строки, выделите строку, которая ниже закрепляемой

2. Перейдите во вкладку «Вид» вверху на панели инструментов и в области с общим названием «Окно» нажмите на «Закрепить области». Тут же находятся инструменты «Закрепить верхнюю строку» и «Закрепить первый столбец». После нажатия моя строка 1 с заголовком «РАСХОДЫ» и месяцами будет неподвижной при прокрутке и заполнении нижней части таблицы.

Инструмент для закрепления строки, столбца и области

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

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

Выделил 4-ю строку и нажал на «Закрепить области».

Чтобы закрепить три первые строки, выделите четвертую и нажмите на «Закрепить область»

Результат изображен на картинке снизу. Все строки кроме первых трех двигаются при прокрутке.

Результат закрепления трех первых строк

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

Чтобы снять любое закрепление (строк, столбцов или области) нажмите на «Снять закрепление областей»

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

Я для закрепления столбца с названием проектов (это столбец B), выдели следующий за ним (это C) и нажал на пункт меню «Закрепить области». Результатом будет неподвижная область столбцов A и B при горизонтальной прокрутке. Закрепленная область в Excel выделяется серой линией.

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

Использование инструмента закрепить область в excel

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

Однако довольно часто необходимо чтобы при горизонтальной и при вертикальной прокрутке строки и столбцы были неподвижны.

Для такого вида закрепления используется тот же инструмент «Закрепить области», только отличается способ указания области для закрепления.

1. В моем примере мне для фиксации при прокрутке необходимо оставить неподвижной все что слева от столбца C и все что выше строки 4. Для этого выделите ячейку, которая будет первая ниже и правее этих областей. В моем случае это ячейка C4.
2. Во вкладке «ВИД» нажмите «Закрепить области» и в выпадающем меню одноименную ссылку «Закрепить области».
3. Результатом будет закрепленные столбцы и строки.

Результат закрепления области столбцов и строк

Мастер ВПР — простой способ писать сложные формулы

Интерактивный мастер ВПР  проведет вас через необходимые параметры конфигурации поиска, чтобы построить идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если будет нужно извлечь значения слева от столбца поиска.

Вот что вам нужно сделать, чтобы получить формулу под вашу задачу:

1. Запустите мастер кнопкой Vlookup Wizard на ленте Ablebits Data.

1. Выберите свою основную таблицу (Your table) и таблицу поиска (Lookup table).
2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
   • Ключевой столбец (Key column) — находится в вашей основной таблице, содержит значения для поиска.
   • Столбец поиска (Lookup column) — в кот ором будем искать.
   • Возвращаемый столбец (Return column) — из него будем получать значения.
3. Нажмите кнопку Вставить (Insert).

Давайте посмотрим всё в действии.

Стандартный ВПР.

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

Как и в предыдущих примерах, наша задача — подобрать цену для каждого товара, извлекая ее из прайс-листа. Ежели область поиска (Цена) является крайним левым столбцом в зоне поиска, то вставляется обычная формула ВПР для точного соответствия:

Ничего руками писать не нужно.

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

«Левый» ВПР.

Когда колонка результата (Цена) находится слева от области поиска (Прайс),  то мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:

Мы рассматривали левый ВПР в отдельной статье. Там же вы можете посмотреть и формулы для ручного ввода. Здесь же мы получаем их автоматически, не вникая в тонкости синтаксиса и правильности написания.

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

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

Примеры использования функции ВПР:

Как закрепить области в Excel?

Помимо фиксации по отдельности строк и столбцов мы также можем закрепить области в виде их комбинации. Например, одновременно закрепить первую строку и первый столбец.

Для решения такой задачи нам нужно встать в ячейку и запомнить правило закрепления.

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

Другим словами если визуально поделить лист Excel на 4 четверти, то не закреплённой и доступной к прокрутке остаётся только нижняя правая часть, а остальные 3 буду зафиксированы (в зависимости от типа прокрутки):

Поэтому встаём в ячейку выше и левее которой мы планируем закрепить пространство, а далее уже по знакомой схеме идём в панели вкладок и выбираем Закрепить области:

Важный момент.

Закрепить можно только начальные строки и столбцы, например, из середины листа осуществить это уже никак не получится.

Давайте рассмотрим еще пару интересных и полезных примеров.

Как закрепить нужный диапазон в Excel?

Перед тем как фиксировать определенные поля на рабочем листе, надо знать некоторые нюансы:

1. Отметить можно только те строчки или колонки, которые находятся соответственно вверху и слева. Если они расположены в середине рабочего листа, то применить к ним это условие не получится.
2. Процедура будет недоступной, если лист защищен или используется режим редактирования ячейки (для выхода из этого режима достаточно нажать клавишу «Esc» или «Enter»).

В общем, в Экселе можно зафиксировать левую колонку или верхнюю строчку. Если требуется указать и колонку, и ряд, то нужно делать это одновременно. Если же сначала отметить, например, только колонку А, а затем первый ряд, то столбец А будет уже незафиксированным.

Как закрепить столбец или ряд в Excel 2003?

Фиксация полей в разных версиях Excel делается примерно по одному и тому же принципу, но все же этот процесс немного отличается. Первый пример будет на основе версии 2003 года. Для начала необходимо запустить программу, а затем выполнить следующие команды в панели меню: «Окно — Разделить». Теперь рабочий лист будет разделен на 4 ровные части. С помощью этих 2 линий (вертикальной и горизонтальной) и будет осуществляться выбор диапазона для фиксации.

Итак, чтобы зафиксировать шапку в электронной таблице, надо перетянуть эти 2 линии в нужное положение. К примеру, можно указать диапазон из первых 2 строк и 10 колонок. Чтобы его зафиксировать, необходимо выбрать в панели меню команды «Окно — Закрепить области». После этого 2 линии больше нельзя будет передвигать. Вот таким несложным способом получилось в Exel зафиксировать шапку. Теперь при прокрутке таблицы в любую сторону указанные поля всегда будут находиться в верхней части документа (они будут подчеркнуты черными линиями снизу и справа).

Если потребуется выбрать другие ячейки, нужно указать вариант «Снять закрепление» в пункте «Окно», после чего отметить новые ячейки. Если же потребуется полностью отменить фиксацию строк и столбцов, тогда надо отметить пункт «Снять разделение».

Существует еще один, более простой, способ решения этой проблемы. В данном случае нужно всего лишь щелкнуть на ячейку, размещенную ниже и справа от того диапазона, который всегда должен отображаться в верхней части документа. А затем надо выбрать команду «Закрепить». Например, если требуется отметить первые 3 колонки (A, B, C) и первые 2 ряда, то для фиксации понадобится выбрать поле D4 и кликнуть на вышеуказанную команду.

Как зафиксировать диапазон в Excel 2007 и 2010?

Закрепление области в Excel происходит из меню «Вид» — необходимо выбрать меню «Закрепить область», и выбрать тип фиксируемого диапазона

Чтобы зафиксировать поля в версии 2007 и 2010, надо перейти на вкладку «Вид» и в группе «Окно» щелкнуть на кнопку «Закрепить области». Откроется список, в котором можно указать, что именно надо зафиксировать — строчку, столбец или область.

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

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

Фиксация области осуществляется точно так же, как в exel 2003. То есть нужно щелкнуть на определенное поле, находящееся ниже и правее от диапазона, который всегда должен отображаться в верхней части рабочего листа программы.

После применения фиксации ячеек в списке появится другой вариант — «Снять закрепление». Именно этот пункт надо выбрать в том случае, если по какой-то причине понадобилось отключить эту опцию.

Теперь вы знаете, как в Exel зафиксировать нужные поля. С помощью этой опции можно указать определенные ячейки, которые будут отображаться в верхней части рабочего листа Excel (например, шапку таблицы). Благодаря такой возможности программы можно комфортно просматривать даже большие таблицы, при этом всегда будет понятно, где какие данные размещены.

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

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

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

Всё, что слева и выше от этой ячейки, будет зафиксированоСкриншот: Skillbox

Переходим на вкладку «Вид», нажимаем кнопку «Окно» и выбираем «Закрепить области».

Действуем так же, как при закреплении нескольких столбцов или строкСкриншот: Skillbox

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

Дополнительные способы закрепления строк и столбцов

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

• Способ 1. Разделение экрана. В Эксель у вас есть возможность поделить рабочий экран на несколько (две или четыре) области. При этом, вы можете просматривать их отдельно друг от друга, и, прокручивая одну, главную область, ячейки в другой области не будут двигаться. Для этого выберите ячейку ниже либо правее от места, где вам нужно разделить область, и нажмите «Закрепить области» на вкладке «Вид» в панели инструментов exel.
• Способ 2. Умная таблица. Этот способ закрепляет заголовок таблицы exel, делая его независимым от всей таблицы в целом. Чтобы создать такую таблицу, выделите мышкой область, на которой она должна размещаться. Затем вам понадобится комбинация клавиш «Ctrl»+«T/Е». Для правильной работы с фиксацией, шапка таблицы, требующая закрепления, должна состоять из одной строки, то есть для более сложных конфигураций заголовка в таблицах эксель этот способ вряд ли подойдет.

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

Как добавить колонку в таблице excel? Для того, чтобы добавить в таблицу столбец, проводим те же действия, как и для добавления строки, и он встанет левее вашей ячейки.

Как закрепить ячейку в формуле

Иногда вам может понадобиться оставить без изменений адрес ячейки либо адрес столбца или строчки, так как они являются частью формулы. Часто может произойти ситуация, когда вам понадобиться вставить только пару ячеек, но из-за них формула смещается и перестает работать. Следовательно, нужно зафиксировать значение ячейки. Порядок действий для закрепления ячеек в формуле excel следующий:

• Кликните один раз по ячейке, в которой находится необходимая формула.
• Дважды кликните в строке формул на номер нужной ячейки (например, А1). Она выделится.
• Нажмите клавишу «F4». Она дает одну из самых полезных функций в excel.

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

Поставив знак доллара не перед буквой, а перед числом, вы можете закрепить значение в строке таблицы excel. Автоматически это можно сделать двойным нажатием на клавишу «F4».

Три раза нажав на клавишу «F4», вы сможете сделать неизменной ссылку на нужную колонку, при этом закрепится строка excel.

Как закрепить область в excel

Разберем подробнее закрепление областей в excel. Прежде всего, нужно запомнить, что для закрепления нужной вам области нужно выделить ячейку, которая находится под областью, которую следует закрепить, и правее от нее. Таким образом, наживая кнопку «Закрепить область» в excel вы будете знать, что закрепляться будет часть таблицы выше и левее выделенной ячейки, а значит, вам будет проще сориентироваться по таблице. Этот способ также поможет вам, если возникла необходимость закрепить область печати в excel.

Как выполняется открепление областей

Неопытные пользователи Microsoft Office Excel не знают, как открепить зафиксированные ранее области. Здесь все просто, главное – соблюдать некоторые рекомендации:

1. Открыть документ Excel. После появления рабочего поля в табличке не нужно выделять какие-либо ячейки.
2. Перейти во вкладку «Вид» в ленте параметров сверху окна программы.
3. Теперь надо кликнуть по кнопке «Окно», чтобы открыть подраздел с закреплением элементов.
4. ЛКМ щелкнуть по надписи «Снять закрепление областей».
5. Проверить результат путем пролистывания таблицы вниз. Фиксация выделенных ранее ячеек должна отмениться.

Процесс открепления областей в Microsoft Office Excel

Закрепляем столбцы

Первый столбец нашей таблицы — названия марок и моделей автомобилей. Он выделен жёлтым цветом на рисунке ниже. Закрепим его так, чтобы при прокрутке вправо всегда было видно, о каком автомобиле идёт речь:

1. Выделяем любую ячейку таблицы.
2. Переходим на вкладку «Вид».
3. Нажимаем кнопку «Окно» и выбираем опцию «Закрепить первый столбец».

Порядок действий для закрепления первого столбца в ExcelСкриншот: Skillbox

Готово — справа от первого столбца появилась граница. Теперь марки и модели автомобилей всегда отображаются при прокрутке вправо.

Теперь всегда видно, к какому авто относится цена и другие параметрыСкриншот: Skillbox

Допустим, нам для работы постоянно нужна информация из первых двух столбцов — они выделены жёлтым на скриншоте. Закрепить их можно в два шага.

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

Выделяем первую ячейку в столбце, который идёт следующим за закрепляемымиСкриншот: Skillbox

Затем переходим на вкладку «Вид», нажимаем кнопку «Окно» и выбираем опцию «Закрепить области».

Порядок действий для закрепления нескольких столбцовСкриншот: Skillbox

Готово — граница появилась справа от второго столбца. Теперь в любой области таблицы отображаются марки и модели автомобилей и год их выпуска.

Создание сводной таблицы в Excel

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

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

Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.