Простой способ зафиксировать значение в формуле Excel
Сегодня я бы хотел поделиться с вами такой небольшой хитростью, как можно правильно зафиксировать значение в формуле Excel. К сожалению, очень мало пользователей используют таким удобным функционалом табличного процессора, а это жаль. Часто многие сталкивались с такой ситуацией что возникает необходимость сдвинуть или скопировать формулы, но вот незадача, адреса ячеек также уходили «налево» и результата невозможно было получить. А для получения нужного результата, нам окажет помощь доллар, а точнее знак «$», вот именно он является самым главным условием что бы закрепить значение в ячейках.
Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:
Полная фиксация ячейки
Полная фиксация ячейки — это когда закрепляется значение по вертикали и горизонтали (пример, $A$1), здесь значение никуда не может сдвинутся, так называемая абсолютная формула. Очень удобно такой вариант использовать, когда необходимо ссылаться на значение в ячейке, такие как курс валют, константа, уровень минимальной зарплаты, расход топлива, процент доплат, кофициент и т.п.
В примере у нас есть товар и его стоимость в рублях, а нам нужно узнать он стоит в вечнозеленых долларах. Поскольку, обменный курс у нас постоянная ячейка D1, в которой сам курс может меняться исходя из экономической ситуации страны. Сам диапазон вычисление находится от E4 до E7. Когда мы в ячейку Е4 пропишем формулу =D4/D1, то в результате копирования, ячейки поменяют адреса и сдвинутся ниже, пропуская, так необходимый нам обменный курс. А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;
Фиксация формулы в Excel по вертикали
Частичная фиксация по вертикали (пример $A1), это закрепления только столбцов, возможность сдвига формулы частично сохраняется, но только по горизонтали (в строке). Как видно со скриншота или скачанного вами файла с примером.
Фиксация формул по горизонтали
Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдущем пункте, но немножко наоборот. Рассмотрим данный пример подробнее. У нас есть товар, продаваемый, в разных городах и имеющие разную процентную градацию наценок, а нам необходимо высчитать какую наценку и где мы будем ее получать. В диапазоне K1:M1 мы проставили процент наценки и эти ячейки у нас должны быть закреплены для автоматических вычислений. Диапазон для написания формул у нас является К4:М7, здесь мы должны в один клик получить результаты просто правильно прописав формулу. Растягивая формулу по диагонали, мы должны зафиксировать диапазон процентной ставки (горизонталь) и диапазон стоимости товара (вертикаль). Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.
Производя подобные вычисления очень легко и быстро делать перерасчёт на разнообразнейшие варианты, изменив всего 1 цифру. В файле примера вы сможете проверить это изменив всего курс валюты или региональные проценты. И такие вычисление, будут в несколько раз быстрее нежели, другие варианты написание формул в Excel и количество ошибок будет значительно меньше. Но необходимость этого надо увидеть исходя с вашей текущей задачи и проводить фиксацию значения в ячейках стоит в ключевых местах.
Что бы постоянно не переключать раскладку клавиатуры при прописании знака «$» для закрепления значение в формуле, можно использовать «горячую» клавишу F4. Если курсор стоит на адресе ячейки, то при нажатии, будет автоматически добавлен знак «$» для столбцов и строчек. При повторном нажатии, добавится только для столбцов, еще раз нажать, будет только для строк и 4-е нажатие снимет все закрепления, формула вернется к первоначальному виду.
А на этом у меня всё! Я очень надеюсь, что вы поняли все варианты как возможно зафиксировать ячейку в формуле. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Деньги — нерв войны. Марк Туллий Цицерон
Excel 2010-Замораживание областей листа
Способность замораживания отдельных строк или столбцов на листе может быть очень полезной функцией в Excel 2010. Она называется замораживанием панелей. Когда вы выбираете Закрепить области, строки или столбцы, которые останутся видимыми все время. Это особенно полезно при работе с большими таблицами.
>>Посмотреть оглавление уроков
Скачать Сотни Шаблонов для Excel:
Таблицы и формы, Формулы и расчеты, Отчеты и бюджеты. Большая коллекция!
Word 2010. Excel 2010. PowerPoint 2010. Outlook 2010. SharePoint 2010. Access 2010. Publisher 2010. OneNote 2010.
Чтобы закрепить строки:
Выберите строку ниже строки, которые нужно заморозить. Например, если вы хотите строки 1 и 2, чтобы всегда появлялись в верхней части листа даже при прокрутке, затем выберите строку 3.
Нажмите кнопку Просмотр вкладки.
Нажмите кнопку Закрепить области команды. Выпадающего меню.
Выберите Закрепить области.
Черная линия появится ниже строки, которые заморожены на месте. Прокрутка вниз на лист, чтобы увидеть строки ниже замороженных строк.
Для Замораживание столбцов:
Выберите столбец, право на столбцы, которые заморожены. Например, если вы хотите, колонны и B всегда появляется с левой стороны листа, даже при прокрутке, а затем выберите столбец C.
Нажмите кнопку Просмотр вкладки.
Нажмите кнопку Закрепить области команды. Выпадающего меню.
Выберите Закрепить области.
Черная линия представляется право в замороженной области. Прокрутка через лист, чтобы увидеть столбцы справа от замороженных столбцов.
Чтобы разморозить панели:
Нажмите кнопку Просмотр вкладки.
Нажмите кнопку Закрепить области команды. Выпадающего меню.
Выберите разморозить области. Панели разблокируются и черная линия исчезнет.
Открытие существующей книги Excel 2010.
Вставьте новый лист.
Изменить имя листа.
Удалить лист.
Перемещение листа.
Копирование листа.
Попробуйте группировку и разгруппировку листов.
Попробуйте замораживания и размораживания столбцов и строк.
Внимание. Мы будем добавлять новые уроки время от времени на наш сайт, так что занесите в «Закладки» вашего браузера эту страницу или в папку «Избранное», чтобы потом легко вернуться. Также рекомендуем скачать очень полезный Видеокурс Microsoft Excel 2010 для быстрого закрепления знаний.
Следующийурок (Печать)
Как заморозить и разморозить строки и столбцы в Excel
Если вы работаете с большой электронной таблицей, может быть полезно «заморозить» определенные строки или столбцы, чтобы они оставались на экране, пока вы просматриваете остальную часть листа.
Когда вы просматриваете большие листы в Excel, вам может понадобиться держать в поле зрения некоторые строки или столбцы — например, заголовки. Excel позволяет заморозить вещи одним из трех способов:
Вы можете заморозить верхний ряд.
Вы можете заморозить самый левый столбец.
Вы можете заблокировать панель, содержащую несколько строк или несколько столбцов, или даже остановить группу столбцов и группу строк одновременно.
Итак, давайте посмотрим, как выполнять эти действия.
Заморозить верхний ряд
Вот первая таблица, с которой мы будем связываться. Это шаблон списка инвентаря, который поставляется с Excel, на случай, если вы захотите поиграть.
Верхняя строка в нашем примере листа — это заголовок, который может быть полезно держать в поле зрения при прокрутке вниз. Перейдите на вкладку «Вид», откройте раскрывающееся меню «Стоп-панели» и нажмите «Стоп-кадр».
Теперь, когда вы прокручиваете лист вниз, эта верхняя строка остается на виду.
Чтобы изменить это, вам просто нужно разморозить стекла. На вкладке «Вид» снова нажмите на выпадающий список «Стоп-кадр», и на этот раз выберите «Стоп-кадр».
Заморозить левую строку
Иногда крайний левый столбец содержит информацию, которую вы хотите сохранить на экране при прокрутке вправо на листе. Для этого перейдите на вкладку «Вид», откройте раскрывающееся меню «Стоп-панели» и нажмите «Стоп-кадр первой колонки».
Теперь, когда вы прокрутите вправо, этот первый столбец останется на экране. В нашем примере это позволяет нам держать столбец идентификатора инвентаря видимым, пока мы прокручиваем другие столбцы данных.
И снова, чтобы разморозить колонку, просто перейдите в View> Freeze Panes> Unfreeze Panes.
Заморозьте свою собственную группу строк или столбцов
Иногда информация, которую необходимо зафиксировать на экране, отсутствует в верхней строке или первом столбце. В этом случае вам необходимо заблокировать группу строк или столбцов. В качестве примера взгляните на таблицу ниже. Это один из шаблонов Employee Attendance, включенный в Excel, если вы хотите загрузить его.
Обратите внимание, что перед фактическим заголовком есть ряд строк вверху, которые мы можем захотеть заморозить — строка с указанными днями недели. Очевидно, что замораживание только верхней строки в этот раз не сработает, поэтому нам нужно заморозить группу строк в верхней части.
Сначала выберите весь ряд ниже самого нижнего ряда, который вы хотите оставить на экране. В нашем примере мы хотим, чтобы пятая строка оставалась на экране, поэтому мы выбираем шестую строку. Чтобы выбрать строку, просто нажмите номер слева от строки.
Затем перейдите на вкладку «Вид», откройте раскрывающееся меню «Стоп-панели» и нажмите «Стоп-панели».
Теперь, когда вы прокручиваете лист вниз, строки с первой по пятую замораживаются. Обратите внимание, что толстая серая линия всегда показывает, где находится точка замерзания.
Вместо этого, чтобы зафиксировать панель столбцов, просто выделите всю строку справа от самой правой строки, которую вы хотите зафиксировать. Здесь мы выбираем строку C, потому что хотим, чтобы строка B оставалась на экране.
А затем перейдите в View> Freeze Panes> Freeze Panes. Теперь наша колонка, показывающая месяцы, остается на экране, когда мы прокручиваем вправо.
И помните, что если вы заморозили строки или столбцы и вам необходимо вернуться к обычному виду, просто перейдите в «Просмотр»> «Замораживание панелей»> «Разморозка панелей».
Замораживание столбцов и строк одновременно
У нас есть еще один трюк, чтобы показать вам. Вы видели, как заморозить группу строк или группу столбцов. Вы также можете зафиксировать строки и столбцы одновременно. Снова посмотрим на электронную таблицу Employee Attendance. Допустим, мы хотим, чтобы заголовок с днями (пятая строка) и столбца с месяцами (столбец B) отображался на экране одновременно.
Для этого выберите самую верхнюю и левую ячейки, которые не хотите замораживать. Здесь мы хотим заморозить строку пять и столбец B, поэтому мы выберем ячейку C6, щелкнув по ней.
Затем перейдите на вкладку «Вид», откройте раскрывающееся меню «Стоп-панели» и нажмите «Стоп-панели».
И теперь мы можем прокрутить вниз или вправо, сохраняя строки и столбцы заголовка на экране.
Замораживание строк или столбцов в Excel не составляет труда, если вы знаете, что есть возможность. И это действительно может помочь при навигации по большим, сложным таблицам.
Как заморозить и разблокировать строки и столбцы в Excel
Если вы работаете с большой электронной таблицей, может быть полезно «заморозить» определенные строки или столбцы, чтобы они оставались на экране при прокрутке остальной части листа.
Когда вы прокручиваете большие листы в Excel, вам может потребоваться, например, сохранить некоторые строки или столбцы, подобные заголовкам. Excel позволяет вам замораживать вещи одним из трех способов:
Вы можете заморозить верхний ряд.
Вы можете заморозить крайний левый столбец.
Вы можете заморозить панель, содержащую несколько строк или несколько столбцов, или даже заморозить группу столбцов и группу строк одновременно.
Итак, давайте посмотрим, как выполнять эти действия.
Заморозить верхнюю строку
Вот первая электронная таблица, с которой мы будем общаться. Это шаблон списка инвентаря, который поставляется с Excel, если вы хотите играть вместе.
Верхний ряд в нашем примере — это заголовок, который может быть приятным, если вы прокрутите вниз. Перейдите на вкладку «Просмотр», выберите раскрывающееся меню «Заморозить панорамирование» и нажмите «Завершить верхнюю строку».
Теперь, когда вы прокручиваете лист вниз, эта верхняя строка остается в поле зрения.
Чтобы отменить это, вам просто нужно разморозить панели. На вкладке «Вид» снова нажмите раскрывающееся меню «Стоп-кадры», и на этот раз выберите «Освободить панорамы».
Заморозить левую строку
Иногда в самом левом столбце содержится информация, которую вы хотите сохранить на экране при прокрутке вправо на вашем листе. Для этого перейдите на вкладку «Просмотр», выберите раскрывающееся меню «Замораживание» и нажмите «Завершить первый столбец».
Теперь, когда вы прокрутите направо, этот первый столбец остается на экране. В нашем примере это позволяет нам видеть столбец идентификатора инвентаря видимым, когда мы просматриваем другие столбцы данных.
И снова, чтобы разморозить колонку, просто выберите «Просмотр»> «Стоп-кадр»> «Освободить панорамы».
Заморозить свою группу строк или столбцов
Иногда информация, которую нужно заморозить на экране, не находится в верхней строке или первом столбце. В этом случае вам необходимо заморозить группу строк или столбцов. В качестве примера рассмотрим таблицу ниже. Это шаблон шаблона Employee Attendance, включенный в Excel, если вы хотите его загрузить.
Обратите внимание, что есть куча строк вверху до фактического заголовка, который мы можем заморозить, — строки с указанными днями недели. Очевидно, что замораживание только верхней строки не будет работать на этот раз, поэтому нам нужно заморозить группу строк вверху.
Сначала выберите всю строку ниже самой нижней строки, которую вы хотите оставить на экране. В нашем примере мы хотим, чтобы пятая строка оставалась на экране, поэтому мы выбираем шестую строку. Чтобы выбрать строку, просто нажмите номер слева от строки.
Затем перейдите на вкладку «Просмотр», щелкните раскрывающееся меню «Заморозить панорамирование» и нажмите «Заморозить панорамирование».
Теперь, когда вы прокручиваете лист, ряды от одного до пяти замораживаются. Обратите внимание, что толстая серая линия всегда показывает вам, где находится точка замораживания.
Чтобы вместо этого заблокировать панель столбцов, просто выберите целую строку справа от правой строки, которую вы хотите заморозить. Здесь мы выбираем Row C, потому что хотим, чтобы Row B оставался на экране.
А затем выберите «Просмотр»> «Стоп-кадры»> «Стоп-кадры». Теперь, наша колонка показывает, что месяцы остаются на экране, когда мы прокручиваем направо.
И помните, что когда вы заморозили строки или столбцы и вам нужно вернуться к нормальному виду, просто перейдите в меню «Просмотр»> «Стоп-кадр»> «Освободить панорамы».
Стоп-кадры и строки в одно и то же время
У нас есть еще один трюк, чтобы показать вам. Вы видели, как заморозить группу строк или группу столбцов. Вы также можете заморозить строки и столбцы одновременно. Еще раз посмотрим на таблицу рабочих мест сотрудников, скажем, мы хотели сохранить оба заголовка в будние дни (строка пятая)а также столбец с месяцами (столбец B) на экране одновременно.
Для этого выберите самую верхнюю и левую ячейку, которые выне хотите заморозить. Здесь мы хотим заморозить строку пять и столбец B, поэтому мы выберем ячейку C6, щелкнув ее.
Затем перейдите на вкладку «Просмотр», щелкните раскрывающееся меню «Заморозить панорамирование» и нажмите «Заморозить панорамирование».
И теперь мы можем прокручивать вниз или вправо, сохраняя эти строки заголовков и столбцы на экране.
Замораживание строк или столбцов в Excel не сложно, как только вы знаете, что есть опция. И это может реально помочь при навигации по большим, сложным электронным таблицам.
