Как протянуть формулу в excel чтобы менялась одна переменная

Обновлено: 07.01.2025

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

видео может дополнять статью или содержать её полностью

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

Как открыть диспетчер имен в Excel, чтобы посмотреть как он выглядит? В верхнем меню выберите раздел «Формулы» и нажмите кнопку «Диспетчер имён» (кнопка обычно находится примерно посередине панели, но вообще это зависит от размера экрана и того, менялись ли стандартные настройки панелей Excel).

Сам Диспетчер имён представляет собой всплывающее окно, которое показано на скриншоте ниже. На рисунке показан Диспетчер имен в Excel 2010 версии программы, но в целом его вид практически не различается от версии к версии (к примеру, Диспетчер имен в Excel 2016 практически такой же).

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

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

Как использовать Диспетчер имён в Excel

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

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

Фактически можно считать, что Excel позволяет создавать переменные вида «x=10», при этом слева будет имя переменной, а справа — её значение.

Как задать имя и изменить его

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

Имя (с учётом ограничений, налагаемых на имена переменных в Excel);
Область видимости (поле «Область»);
Значение переменной (параметр «Диапазон»);

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

Ограничения для имён

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

Правила задания имён переменных следующие:

Допустимые символы
Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания. В качестве определенного имени нельзя использовать буквы "C", "c", "R" и "r", поскольку эти буквы используются как сокращенное имя строки и столбца выбранной в данный момент ячейки при их вводе в поле Имя или Перейти.
Имена в виде ссылок на ячейки запрещены
Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1.
Пробелы не допускаются
Использовать в имени пробелы нельзя. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.), как в примерах "Налог_с_Продаж" и "Первый.Квартал".
Имя может содержать до 255-ти символов
Учет регистра
Имя может состоять из строчных и прописных букв. Excel не различает строчные и прописные буквы в именах. Например, если создать имя "Продажи" и затем создавать имя "ПРОДАЖИ", Excel предложит выбрать уникальное имя.

Например, мы можем создать переменную с именем «ЧислоПИ». Это правильное имя.

Использование переменных

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

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

Что ещё можно сказать о переменных в Excel

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

Напомним, что в Excel адресом объединённой ячейки является верхняя левая ячейка диапазона, объединением которого и была получена ячейка.

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

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

Примеры заданных переменных и их использование

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

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

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

Смотреть видео
Диспетчер имен в Excel

Прикреплённые документы

Определение и использование имен в формулах Excel.pdf

Файлы для загрузки

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

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

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

1 Простое протягивание формулы

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

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

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

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

=IF (VLOOKUP(A1, B:B, 1, 0) > 10, VLOOKUP(A1, B:B, 1, 0) - 10, VLOOKUP(A1, B: B, 1, 0) )

[выше приведен простой пример проблемы, а не конкретная формула, с которой я работаю.]

вы можете определить имя для части VLOOKUP формулы.

Шаг (1) важен здесь: я думаю, во второй строке вы хотите использовать Excel VLOOKUP(A2,B:B, 1, 0) третья строка VLOOKUP(A3,B:B, 1, 0) , etc. Шаг (4) достигает этого, используя относительные ссылки ( A1 и B:B ), а не абсолютные ссылки ( $A и $B:$B ).

вы можете хранить промежуточные значения в ячейке или столбце (которые вы можете скрыть, если выберете)

не связанные с переменными, ваш пример также будет решен с помощью MOD :

VLOOKUP функция в собственной ячейке: =VLOOKUP(A1, B:B, 1, 0) (В С1), то формула ссылается С1: =IF( C1 > 10, C1 - 10, C1 )
создать UDF:

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

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

да. Но не напрямую.

вы можете опубликовать Vlookup () в одной ячейке и использовать его адрес там, где это необходимо. - Это, пожалуй, единственный прямой способ использования переменных в Excel.

вы можете определить Vlookup (reference)-10 как функцию-оболочку из макросов VBE. Нажмите Alt+f12 и используйте эту функцию

Я знаю, что это немного не по теме, но следуя решению, представленному Йонасом Бемером, на самом деле я думаю, что MOD-лучшее решение для вашего примера.

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

ie. Предположим, что VLOOKUP (A1, B:B, 1, 0) возвращает 23. Ваша формула IF просто сделает этот расчет: 23-10 и вернет 13 в результате.

с другой стороны, MOD (VLOOKUP(A1, B: B, 1, 0), 10) разделит 23 на 10 и покажет остаток: 3.

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

12 строка) группа 1 (199 ячеек)
212 строка) группа 2 (199 ячеек)
и т.д..

как добавлять данные дальше формулой типа того

чтобы заполнять ячейки быстро и не высчитывать строку, сколько строк будет под 199 штук

Вопрос задан более двух лет назад
2854 просмотра

Простой 1 комментарий

Либо с помощь vba, либо формулы писать. Но, как говорится "Невнятный тз - результат хз"

1 - Выделяешь ячейку с формулой
2 - Тянешь выделение на нужный размер (клавиши стрелка при нажатой shift)
3 - Получается выделенная область с активной ячейкой там где формула
4 - Нажимаешь F2 - режим правки (в ячейке с формулой появится курсор)
5 - Нажимаешь Ctrl + Enter и Ваша Формула скопирована на все выделение.

растягивает формулу на (199 - 12) ячеек вниз.
Для использования нужно поставить курсор на ячейку и запустить макрос на выполнение.
В дальнейшем можно установить и запускать макрос комбинацией клавиш.
Также можно автоматизировать растягивание формулы во всей колонке таблицы одной кнопкой, но предварительно нужно смотреть структуру таблицы.

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

Нажать Ctrl+D. Это заполнит формулой (или значением, если там не формула) из верхней ячейки все выделенные ячейки.

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

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене - $50 и определенный % книг по более низкой цене - $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Таблица данных с одной переменной.

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

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А.

3. Выберите диапазон A12:B17.

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.

5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4.

Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода ("Подставлять значения по столбцам в: ") мы оставляем пустой.

Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.

Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.

Таблица данных с двумя переменными.

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

1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).

2. Внесите различные варианты высокой цены в строку 12.

3. Введите различные проценты в столбце А.

4. Выберите диапазон A12:D17.

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

5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.

6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7.

7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4.

Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.

Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.

Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.

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

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

Читайте также: