Создание и изменение ссылки на ячейку

Создание и изменение ссылки на ячейку

ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel на значения или данные, которые нужно использовать в формуле.

Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:

данные из одной или нескольких смежных ячеек на листе;

данные из разных областей листа;

данные на других листах той же книги.

Значение в ячейке C2

Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

Ячейки с именами «Актив» и «Пассив»

Разность значений в ячейках «Актив» и «Пассив»

Диапазоны ячеек «Неделя1» и «Неделя2»

Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива

Ячейка B2 на листе Лист2

Значение в ячейке B2 на листе Лист2

Щелкните ячейку, в которую нужно ввести формулу.

В строка формул введите = (знак равенства).

Выполните одно из следующих действий:

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

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

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

Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.

Выполните одно из указанных ниже действий.

Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.

Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

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

Примечание: Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажать клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

Щелкните ячейку, в которую нужно ввести формулу.

В строка формул введите = (знак равенства) и формулу, которую вы хотите использовать.

Щелкните ярлычок листа, на который нужно сослаться.

Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

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

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

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

Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.

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

Нажмите клавиши CTRL + C или перейдите на вкладку Главная и в группе буфер обмена нажмите кнопку Копировать .

Нажмите клавиши CTRL + V или перейдите на вкладку Главная , в группе буфер обмена нажмите кнопку Вставить .

По умолчанию при вставке скопированных данных появляется кнопка Параметры вставки .

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

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

Выполните одно из следующих действий:

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

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

В строка формул выделите ссылку в формуле и введите новую ссылку .

Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.

Примечание: Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажать клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Выполните одно из указанных ниже действий.

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

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

На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.

Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.

Выделите ячейку с формулой.

В строке формул строка формул выделите ссылку, которую нужно изменить.

Для переключения между типами ссылок нажмите клавишу F4.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник:
http://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D0%B8-%D0%B8%D0%B7%D0%BC%D0%B5%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5-%D1%81%D1%81%D1%8B%D0%BB%D0%BA%D0%B8-%D0%BD%D0%B0-%D1%8F%D1%87%D0%B5%D0%B9%D0%BA%D1%83-c7b8b95d-c594-4488-947e-c835903cebaa

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

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

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

Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2

Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.

Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.

Читайте также  Как сделать страницы в excel в 2010?

Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2* B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3* B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/ $B$7 , вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.

Примечание: в рассматриваемом примере мы указал два значка доллара $ B $ 7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7 , встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B $ 7 (зафиксирована строка 7) или $ B7 (зафиксирован только столбец B)

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

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.

Источник:
http://sirexcel.ru/osvaivaem-excel/osnovy/kak-v-excel-zakrepit-zafiksirovat-yachejku-v-formule/

Как зафиксировать ссылку в Excel?

Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?

Это необходимо для того, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась. Например, для расчета коэффициента сезонности января (см. вложение) мы средние продажи за январь (пункт 2 см. вложение) делим на среднегодовые продажи за 3 года (пункт 3 см. вложение). Если мы просто протянем ячейку вниз, чтобы рассчитать коэффициенты для других месяцев, то для февраля мы получим, что среднегодовые продажи за февраль разделятся на ноль, а не на среднегодовые продажи за 3 года.

Как зафиксировать ссылку на ячейку, чтобы, когда мы протягивали формулу, ссылка не смещалась?

Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:

и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:

это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.

Если еще раз нажмете клавишу «F4», то ссылка станет $F4:

Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.

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

Если зафиксирована только строка (R), то ссылка будет R 4 C[-1]

Если зафиксирован только столбец (С), то ссылка будет иметь вид R C6

Для того, чтобы зафиксировать диапазон, необходимо его выделить в строке формул в Excel и нажать клавиши “F4”.

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

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Источник:
http://4analytics.ru/chto-vajno-znat-o/kak-zafiksirovat-ssilku-v-excel.html

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

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

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

Что такое ссылка Excel

Лист состоит из ячеек. Каждая из них содержит определенную информацию. Другие ячейки могут ее использовать в вычислениях. Но как они понимают, откуда брать данные? Это помогают им сделать ссылки.

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

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

Для того, чтобы сохранить адреса и колонки, и ряда, необходимо выполнить следующие шаги:

  1. Нажать по ячейке, содержащей формулу.
  2. Нажать по строке формул по той ячейке, которая нам нужна.
  3. Нажать F4.

Как следствие, ссылка ячейки изменится на абсолютную. Ее можно будет узнать по характерному знаку доллара. Например, если нажать на ячейку B2, а потом нажать на F4, то ссылка обретет следующий вид: $B$2.

1 2

Что означает знак доллара перед частью адреса на ячейку?

  1. Если он размещается перед буквой, то это говорит о том, что ссылка на столбец остается такой же, независимо от того, куда была перемещена формула.
  2. Если знак доллара находится перед числом, это говорит о том, что закреплена строка.

Этот способ почти такой же самый, как и прошлый, только нажать нужно F4 два раза. например, если у нас была ячейка B2, то после этого она станет B$2. Простыми словами, таким способом у нас получилось зафиксировать строку. При этом буква столбца будет изменяться.

3

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

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

4

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

5

Закрепление ячеек для большого диапазона

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

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

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

  1. Ячейки.
  2. Макросы.
  3. Функции разных типов.
  4. Ссылки и массивы.

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

  1. Выделить диапазон.
  2. Открыть вкладку VBA-Excel, которая появится после установки.
  3. Открыть меню «Функции», где располагается опция «Закрепить формулы».

6

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

    7

    В нашем примере для этого необходимо ввести формулу = B2*C2. Она достаточно простая, как видите. Очень легко на ее примере описывать то, как можно закрепить адрес ячейки или отдельный ее столбец или ряд.

    Можно, конечно, в данном примере попробовать протянуть с помощью маркера автозаполнения формулу вниз, но в таком случае ячейки будут автоматически изменены. Так, в ячейке D3 будет другая формула, где цифры будут заменены, соответственно, на 3. Далее по схеме – D4 – формула обретет вид =B4*C4, D5 – аналогично, но с цифрой 5 и так далее.

    Если так и надо (в большинстве случаев так и получается), то проблем нет. Но если нужно зафиксировать формулу в одной ячейке, чтобы она не изменялась при перетягивании, то сделать это будет несколько сложнее.

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

    Вот, как оно выглядит в нашем примере.

    8

    Если мы аналогично предыдущему варианту попробуем прописать формулу, то потерпим поражение. Точно так же формула изменится на соответствующую. В нашем примере она будет такой: =E3*B8 . Отсюда мы можем увидетЬ. что первая часть формулы превратилась в E3, и мы ставим перед собой эту задачу, а вот изменение второй части формулы на B8 нам ни к чему. Поэтому нам нужно превратить ссылку на абсолютную. Можно сделать это и без нажатия на клавишу F4, просто поставив знак доллара.

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

    Сама формула будет выглядеть следующим образом:

    Внимание! Мы указали два знака доллара. Таким образом мы показываем программе, что нужно фиксировать как столбец, так и строку.

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

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

    Для начала нужно понять, что ключевое понятие макроса – объекты, которые могут содержать в себе другие объекты. За электронную книгу (то есть, документ) отвечает объект Workbooks. В его состав входит объект Sheets, который являет собой совокупность всех листов открытого документа.

    Соответственно, ячейки – это объект Cells. Он содержит все ячейки определенного листа.

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

    Например, строчка кода, содержащая ссылку на ячейку C5, будет выглядеть так:

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Cells(5, 3)

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Cells(5, “C”)

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

    В этом случае строчка будет выглядеть следующим образом.

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Range(“C5”)

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

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

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

    Источник:
    http://office-guru.ru/excel/formuly-funkcii/kak-zakrepit-yachejku-v-formule-excel.html

    Неизменяемая ячейка в excel

    Как закрепить в Excel заголовок, строку, ячейку, ссылку, т.д.

    ​Смотрите также​​Источник: https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/​ заказы, ожидаемые в​ от того, где​ оранжевой;​ это совсем не​​Вместо​
    ​ значений.​Чтобы удалить все стрелки​ ​ крайних подальше от​
    ​ отследить связи между​ а затем —​ на диапазон ячеек,​ в конце текстового​ она содержит числовое​ в​ то выделяем первую​Рассмотрим,​Перевел: Антон Андронов​
    ​ течение 1 и​ именно в ячейке​ ​Если заказ доставлен (значение​
    ​ сложно. Далее мы​C2​В одной из предыдущих​ трассировки на листе,​ активной ячейки. На​ этими ячейками и​ клавишу ВВОД. При​ функция ЯЧЕЙКА возвращает​ значения добавляется «()».​ значение, а не​Excel.​ строку. Нажимаем и​как закрепить в Excel​Автор: Антон Андронов​ 3 дней, розовым​
    ​ он находится. В​Delivered​ ​ рассмотрим ещё несколько​​Вы можете ввести​
    ​ статей мы обсуждали,​ на вкладке​ вкладке​ формулами с стрелки​ необходимости измените ширину​ сведения только для​Примечание:​ текст, можно использовать​Для этого нужно​ удерживаем нажатой клавишу​ строку, столбец, шапку​Deadjinn​ цветом, а те,​ примере таблицы на​), то заливка таких​
    ​ примеров формул и​ ​ ссылку на другую​​ как изменять цвет​
    ​формулы​формулы​ трассировки, как показано​ столбцов, чтобы видеть​ левой верхней ячейки​ Это значение не поддерживается​ следующую формулу:​ провести обратное действие.​ «Ctrl» и выделяем​ таблицы, заголовок, ссылку,​: Выделяем те ячейки,​ которые будут выполнены​ рисунке ниже столбец​ ячеек должна быть​ парочку хитростей для​ ячейку Вашей таблицы,​
    ​ ячейки в зависимости​ ​в группе​
    ​в группе​ на этом рисунке.​ все данные.​ диапазона.​ в Excel Online,​=​Например, чтобы убрать​ следующие строки, ячейки,​ ячейку в формуле,​ которые НЕ будут​ в течение 5​
    ​Delivery​ зелёной;​ решения более сложных​ значение которой нужно​ от её значения.​Зависимости формул​Зависимости формул​
    ​Выполните следующие действия для​Данные​В приведенном ниже списке​ Excel Mobile и​ЕСЛИ(​ закрепленную область печати,​
    ​ т.д.​ картинку в ячейке​ блокироваться.​
    ​ и 7 дней,​(столбец F) может​Если срок доставки заказа​ задач.​ использовать для проверки​ На этот раз​, нажмите кнопку​нажмите стрелку рядом​ отображения формулы отношений​
    ​75​ описаны текстовые значения,​ Excel Starter.​ ​ЯЧЕЙКА(«тип», A1) = «v»;​ заходим на закладке​На закладке «Разметка​и другое.​Тыкаем правой кнопкой​ жёлтым цветом. Формулы​ содержать текст «Urgent,​ находится в прошлом​В таблице из предыдущего​ условия, а вместо​ мы расскажем о​Убрать стрелки​
    ​ с кнопкой​ ​ между ячейками.​
    ​Привет!​ возвращаемые функцией ЯЧЕЙКА,​»скобки»​ A1 * 2;​ «Разметка страницы» в​ страницы» в разделе​Как закрепить строку и​ по выделенному и​ будут выглядеть так:​ Due in 6​ (значение​ примера, вероятно, было​4​ том, как в​.​Убрать стрелки​Выберите​Формула​ если в качестве​1, если форматированием ячейки​ 0)​
    ​ раздел «Параметры страницы».​ «Параметры страницы» нажимаем​ столбец в​ выбираем ФОРМАТ ЯЧЕЙКИ​=ИЛИ($F2=»Due in 1 Days»;$F2=»Due​ Hours» (что в​Past Due​ бы удобнее использовать​можете указать любое​ Excel 2010 и​Если Excel звуковых сигналов​и нажмите кнопку​
    ​файл​Описание​ ​ аргумента «тип_сведений» указано​
    ​ предусмотрено отображение положительных​Эта формула вычисляет произведение​
    ​ Нажимаем на кнопку​ на кнопку функции​Excel.​ – ЗАЩИТА.​ in 3 Days»)​ переводе означает –​), то заливка таких​ разные цвета заливки,​ нужное число. Разумеется,​ 2013 выделять цветом​
    ​ при нажатии кнопки​Убрать стрелки влияющие​>​Результат​ значение «формат», а​ или всех чисел​ A1*2, только если​ функции «Область печати»​ «Область печати». В​В Excel можно​
    ​Снимаем галочку, «Защищаемая​=OR($F2=»Due in 1 Days»,$F2=»Due​ Срочно, доставить в​ ячеек должна быть​ чтобы выделить строки,​
    ​ в зависимости от​ строку целиком в​Зависимые​. Чтобы удалить​

    ​Параметры​=ЯЧЕЙКА(«строка»;A20)​ аргумент ссылки указывает​ в круглых скобках;​ в ячейке A1​ и выбираем из​ появившемся окне нажимаем​ закрепить верхнюю строку​

    ЯЧЕЙКА (функция ЯЧЕЙКА)

    ​ ячейка»​​ in 3 Days»)​ течение 6 часов),​ красной.​ содержащие в столбце​ поставленной задачи, Вы​ зависимости от значения​или​ другой уровень стрелки,​>​Номер строки ячейки A20.​ на ячейку, отформатированную​ во всех остальных​ содержится числовое значение,​ появившегося окна функцию​ на слово «Задать».​ и левый первый​Затем защищаем весь​=ИЛИ($F2=»Due in 5 Days»;$F2=»Due​ и эта строка​И, конечно же, цвет​Qty.​

    ​ можете использовать операторы​ одной ячейки, а​Влияющие​ нажмите кнопку еще​Advanced.​20​ с использованием встроенного​ случаях — 0.​ и возвращает значение​ «Убрать».​​Когда зададим первую​​ столбец, закрепить несколько​

    ​ лист​ in 7 Days»)​ также будет окрашена.​ заливки ячеек должен​различные значения. К​ сравнения меньше (​ также раскроем несколько​, Excel или​ раз.​Примечание:​=ЯЧЕЙКА(«содержимое»;A3)​

    ​ числового формата.​ ​Примечание:​ ​ 0, если в​Чтобы отменить фиксацию​ область печати, в​

    ​ строк и столбцов,​Если в 2003,​=OR($F2=»Due in 5 Days»,$F2=»Due​Для того, чтобы выделить​ изменяться, если изменяется​ примеру, создать ещё​=$C2​ хитростей и покажем​ найдены все уровни​

    ​ Если вы используете Excel​Содержимое ячейки A3.​

    ​Формат Microsoft Excel​​ Это значение не поддерживается​ ячейке A1 содержится​ верхних строк и​ диалоговом окне «Область​ область, т.д. Смотрите​ то СЕРВИС –​ in 7 Days»)​ цветом те строки,​

    Источник:
    http://my-excel.ru/excel/neizmenjaemaja-jachejka-v-excel.html

    «Excel». Как сделать, чтобы ячейки в формуле не менялись при копировании?

    Как сделать, чтобы в формуле значение одной ячейки не менялось и осталось тем же самым при копировании?

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

    Лучше всего разобрать на примере.

    Допустим в ячейке у Вас формула

    если Вы растяните ее вниз (или скопируете и вставите вниз — в следующую ячейку)

    то формула преобразуется и станет вот такой:

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

    Сделайте так и ячейка с «долларами» (то есть $B$2 в нашем случае) не будет меняться.

    Значки доллара ($) можно проставить как вручную введя их с клавиатуры, так и через F4.

    Использовать F4 надо так: мышкой выделяете (в формуле) нужный адрес (в нашем примере — это B2) и нажимаете 1 раз F4 — сразу и перед буквой и перед цифрой появится значок доллара $ (он, кстати, в Excel означает, т.н. абсолютный адрес — то есть такой адрес, который не будет изменяться при копировании формулы)

    «Excel» — это популярный документ, который дает много возможностей. Иногда существуют некоторые нюансы, которые приходится соблюдать, чтобы некоторые моменты не создавали неудобства.

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

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

    Первый самый простой.

    Пример. Вот данные, которые получены в столбце «D» суммированием данных из столбцов «В» и «С», разделив результат на данные из столбца «J», чтобы преобразовать в евро.

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

    1) И вот какое решение существует — преобразовать относительные ссылки в абсолютные.

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

    2) Тогда можно попробовать другой способ — «дезактивировать» формулы, то есть сделать так, чтобы в документе «Excel» не воспринимались формулы — «формулами», а воспринимались, словно это обычный текст.

    Тогда знак «=» на какое-то время следует заменить любым другим символом, например, решеткой — «#» или парой амперсандов — «&&».

    Воспользуемся горячими клавишами.

    3) Еще способ — воспользоваться копированием с применением блокнота, то есть из документа копировать в блокнот, оттуда переносить данные в нужный диапазон.

    На вкладке «Формулы» надо найти «Показать формулы» — режим проверки формул, тогда в ячейках вместо результатов программа показывает формулы, по которым вычислили эти результаты. Можно пользоваться горячими клавишами, алгоритм следующий:

    Дальше надо скопировать диапазон из Exel в блокнот —

    4) Четвертый способ подходит тем, кто постоянно выполняет копирование, перенося каждый раз. Можно воспользоваться макросом — здесь все проще всего, все задано заранее, надо только создать макрос.

    Чтобы работать с макросами, надо перейти на вкладку «Разработчик» или применить сочетание горячих клавишь Alt+F8.

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

    В авторском ролике Николай Павлов, специалист по Exel, расскажет еще более подробно и наглядно.

    Документ «Excel» часто бывает необходим, чтобы посчитать значение в различных задачах. Но иногда бывает неудобно работать с этим документом по ряду причин. Так, если попытаться скопировать столбец с формулой в другой диапазон, то формула становится другой и считает неправильно. А чтобы не менялась формула, приходится идти на хитрости.

    Самое простейшее — сделать так, чтобы «Excel» вместо формулы видел текст. Для этого знак «=» надо заменить любым знаком или символом. Например, на «$» или «#». Чтобы это сделать, используют горячие клавиши. Выделили диапазон, нажали сочетание горячих клавиш, чтобы вызвать окно «найти и заменить», там вместо «=» подставили другой символ. Сделали копирование, а потом точно так же обратно вместо символа вернули знак равенства.

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

    Другой вариант — изменить формулу таким образом, чтобы «Excel» воспринимал формулу не как формулу, а как обычный текст. Для этого надо изменить знак равенства на другой. Горячими клавишами открывается окно из вкладки «Главной» — «Найти и выделить» и там смотрим «Заменить» — вместо «равно» — любой знак, ту же «решетку». Конечно, потом все придется поменять обратно, чтобы формулы были формулами.

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

    Источник:
    http://www.bolshoyvopros.ru/questions/1371527-excel-kak-sdelat-chtoby-jachejki-v-formule-ne-menjalis-pri-kopirovanii.html