Как забить формулу в excel с границей от одного до другого
Вы можете быстро скопировать формулы в смежные ячейки, перетащив . При заполнении формул вниз будут заложены относительные ссылки, чтобы гарантировать, что формулы будут корректироваться для каждой строки, если не включить абсолютные или смешанные ссылки перед заполнением формулы вниз.
Чтобы заполнить формулу и выбрать параметры для применения, выполните указанные здесь действия.
Выделите ячейку, которая содержит формулу для заполнения смежных ячеек.
Перетащите маркер заполнения по ячейкам, которые вы хотите заполнить.
Если маркер не отображается, возможно, он скрыт. Чтобы снова отобразить его:
Щелкните Параметры > файла
Выберите пункт Дополнительно.
В окне Параметры правкив поле Включить перетаскивать ячейки и заполнять ячейки.
Чтобы изменить способ заполнения, щелкните небольшой значок Параметры автозаполнители , который появляется после завершения перетаскиванием, и выберите нужный параметр.
Дополнительные сведения о копировании формул см. в статье Копирование и вставка формулы в другую ячейку или на другой лист.
Заполнение смежных ячеек формулами
Для заполнения формулы смежным диапазоном ячеек можно использовать команду Заполнить. Просто сделайте следующее:
Вы выберите ячейку с формулой и смежные ячейки, которые нужно заполнить.
Щелкните Главная > заливкаи выберите Вниз ,Справа,Вверхили Влево.
Сочетания клавиш: Вы также можете нажать CTRL+D, чтобы заполнить формулой столбец, или CTRL+R, чтобы заполнить формулой справа от строки.
Включить вычисление книги
При заполнении ячеек формулы не пересчитыются, если автоматическое вычисление книги не включено.
Вот как его можно включить:
Выберите Файл > Параметры.
Щелкните Формулы.
В области Вычисления в книгевыберите Авто.
Выделите ячейку, которая содержит формулу для заполнения смежных ячеек.
Перетащите вниз или вправо от нужного столбца.
Сочетания клавиш: Можно также нажать CTRL+D, чтобы заполнить формулой ячейку в столбце, или CTRL+R, чтобы заполнить формулой ячейку справа в строке.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Автоматическое определение диапазона "от-до" в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.
Как автоматически выделять диапазоны для выборки ячеек из таблицы?
Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки "граница"). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.
Все, что следует сейчас сделать - это создать возможность легкого и быстрого выбора диапазона, который нас интересует (вписывая номер диапазона в одну из ячеек для выбора).
Динамическое определение границ выборки ячеек
Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:
и копируем ее вниз в оставшиеся ячейки. Везде, где в ячейках соседнего столбца находится значение "граница", функция возвращает номер строки. В противном случае возвращает пустую строку.
Следующим шагом будет динамическое определение адреса для выборки диапазона данных из исходного списка в соответствии с критерием отбора.
Изменяемое значение критерия для управления выборкой данных из таблицы будет указано в ячейке C1. Там мы указываем порядковый номер диапазона, данные которого нас интересуют в конкретный момент.
Как получить адрес диапазона ячеек в Excel?
После чего динамически определим адрес начальной ячейки, с которой будет начинаться диапазон. В C2 вводим следующую формулу:
Во втором аргументе функции НАИМЕНЬШИЙ указывается ссылка на ячейку C1, где находится порядковый номер интересующего нас сектора данных (диапазона). А для функции НАИМЕНЬШИЙ – это значение является порядковым номером наименьшего числа в диапазоне вспомогательного столбца $A$7:$A$22 (первый аргумент).
Аналогичным образом динамически определяем адрес последней ячейки, где должна заканчивается выборка. Для этого в C3 водим формулу:
Как не сложно догадаться во втором аргументе функции НАИМЕНЬШИЙ мы прибавляем единицу чтобы получить следующее по порядку наименьшее значение в вспомогательном столбце $A$7:$A$22. Все просто и красиво – такая должна быть магия!
Это практически все. Дальше используйте свое воображение для применения этой полезной функции автоматической выборки диапазонов данных из таблицы по условию пользователя. Например, воспользуемся условным форматированием.
Автоматическая подсветка цветом диапазонов ячеек по условию
Будем подсвечивать цвет диапазона, который соответствует порядковому номеру указанном в критериях выборки C1.
- Выделите диапазон ячеек C7:C22 и выберите иснтрумент «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».
- В появившемя окне выберите опцию «Использовать формулу для определения форматируемых ячеек». Там же в поле ввода введите такую фомрулу:
- Нажмите на кнопку формат и укажите цвет для подсветки соответствующих ячеек. Например, зеленый.
Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон. Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно.
Проверка вводимых значений в Excel на ошибки
Наконец, вы можете предупредить ошибку в случае ввода неверных (не соответствующего формата) значений (числа меньшего или равного нулю, большего, чем общее количество диапазонов) в качестве номера диапазона, который вы ищете. Все это можете сделать при помощи проверки данных. Перейдите на ячейку для ввода критериев выборки C1 и выберите инструмент: «ДАННЫЕ»-«Проверка данных».
Соответствующая формула «обеспечивающая безопасность» могла бы выглядеть так:
0;$D$7
Нажмите ОК, после внесения всех изменений как показано выше на рисунке.
Теперь при попытке ввода в критерий выборки числа больше чем количество диапазонов в пределах «границ», будет предупреждение для пользователя:
Такая выборка может использоваться в автоматизации других различного рода интересных задач. Здесь же приведен только базовый пример возможностей динамической выборки данных из исходной таблицы.
Работая с таблицами Excel, пользователям часто приходится использовать для вычислений одни и те же формулы. Причем расположенные не просто рядом, а вдоль всей строки или столбца. Работу можно заметно упростить — для этого надо познакомиться с различными способами, как протянуть формулу в Эксель в нужном направлении.
Наши советы помогут работать с обычными суммами значений в выбранном диапазоне ячеек или сложными вычислениями с десятками аргументов. Главное, что при большом количестве формул их будет легко расположить в нужных местах.
1 Простое протягивание формулы
Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца. Он требует выполнения следующих действий:
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.
Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них — автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.
2 Быстрое автозаполнение
Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:
Чаще всего под формулами в Excel подразумевают именно встроенные функции, предназначенные для выполнения расчетов, и куда реже математические формулы, имеющие уже устоявшийся вид.
В этой статье я рассмотрю обе темы, чтобы каждый пользователь нашел ответ на интересующий его вопрос.
Окно вставки функции
Некоторые юзеры боятся работать в Экселе только потому, что не понимают, как именно устроены функции и каким образом их нужно составлять, ведь для каждой есть свои аргументы и особые нюансы написания. Упрощает задачу наличие окна вставки функции, в котором все выполнено в понятном виде.
Для его вызова нажмите по кнопке с изображением функции на панели ввода данных в ячейку.
В нем используйте поиск функции, отобразите только конкретные категории или выберите подходящую из списка. При выделении функции левой кнопкой мыши на экране отображается текст о ее предназначении, что позволит не запутаться.
После выбора наступает время заняться аргументами. Для каждой функции они свои, поскольку выполняются совершенно разные задачи. На следующем скриншоте вы видите аргументы суммы, которыми являются два числа для суммирования.
После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.
Используем вкладку с формулами
В Excel есть отдельная вкладка, где расположена вся библиотека формул. Вы можете использовать ее для быстрого поиска и вставки необходимой функции, а для редактирования откроется то же самое окно, о котором шла речь выше. Просто перейдите на вкладку с соответствующим названием и откройте одну из категорий для выбора функции.
Как видно, их названия тематические, что позволит не запутаться и сразу отобразить тот тип формул, который необходим. Из списка выберите подходящую и дважды кликните по ней левой кнопкой мыши, чтобы добавить в таблицу.
Приступите к стандартному редактированию через окно аргументов функции. Кстати, здесь тоже есть описания, способные помочь быстрее разобраться с принципом работы конкретного инструмента. К тому же ниже указываются доступные значения, которые можно использовать для работы с выбранной формулой.
Ручная вставка формулы в Excel
Опытные пользователи, работающие в Excel каждый день, предпочитают вручную набирать формулы, поскольку так это делать быстрее всего. Они запоминают синтаксис и каждый аргумент, что не только ускоряет процесс, но и делает его более гибким, ведь при использовании одного окна с аргументами довольно сложно расписать большую цепочку сравнений, суммирований и других математических операций.
Для начала записи выделите ячейку и обязательно поставьте знак =, после чего начните вписывать название формулы и выберите ее из списка.
Далее начните записывать аргументы, в чем помогут всплывающие подсказки. По большей части они нужны для того, чтобы не запутаться в последовательности и разделителях.
По завершении нажмите клавишу Enter, завершив тем самым создание формулы. Если все записано правильно, и программе удается рассчитать результат, он отобразится в выбранной ячейке. При возникновении ошибки вы сможете ознакомиться с ее текстом, чтобы найти решение.
Вставка математических формул
В завершение поговорим о математических формулах в Excel, так как тематика статьи подразумевает и вставку таких объектов в таблицу тоже. Доступные уравнения относятся к символам, поэтому для их поиска понадобится перейти на вкладку со вставкой и выбрать там соответствующий раздел.
Из появившегося списка найдите подходящее для вас уравнение или приступите к его ручному написанию, выбрав последний вариант.
На экране появится редактор и блок формулы. Его используйте для перемещения, а сам редактор – для того, чтобы заносить в формулу числа и редактировать ее под себя. Учитывайте, что в этом случае не работают никакие проверки, поэтому правильность написания проверять придется собственноручно.
Это были самые простые способы вставить функции и формулы в Excel. Первые три помогут создать операции, а последний пригодится математикам и тем, кто выполняет сложные расчеты при помощи таблицы и нуждается во вставке математических формул.
Формула, она же функция, – одна из основных составляющих электронных таблиц, создаваемых при помощи программы Microsoft Excel. Разработчики добавили огромное количество разных функций, предназначенных для выполнения как простых, так и сложных расчетов. К тому же пользователю разрешено самостоятельно производить математические операции, что тоже можно назвать своеобразной реализацией формул. Именно о работе с этими компонентами и пойдет речь далее.
Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.
Поиск перечня доступных функций в Excel
Если вы только начинаете свое знакомство с Microsoft Excel, полезно будет узнать, какие функции существуют, для чего предназначены и как происходит их создание. Для этого в программе есть графическое меню с отображением всего списка формул и кратким описанием действия расчетов.
Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.
Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».
В этом окне переключите категорию на «Полный алфавитный перечень», чтобы в списке ниже отобразились все доступные формулы в Excel, расположенные в алфавитном порядке.
Выделите любую строку левой кнопкой мыши и прочитайте краткое описание снизу. В скобках показан синтаксис функции, который необходимо соблюдать во время ее написания, чтобы все аргументы и значения совпадали, а вычисления происходило корректно. Нажмите «Справка по этой функции», если хотите открыть страницу о ней в официальной документации Microsoft.
В браузере вы увидите большое количество информации по выбранной формуле как в текстовом, так и в формате видео, что позволит самостоятельно разобраться с принципом ее работы.
Отмечу, что наличие подобной информации на русском языке, еще и в таком развернутом виде, делает процесс знакомства с ПО еще более простым, особенно когда речь идет о переходе к более сложным функциям, действующим не совсем очевидным образом. Не стесняйтесь и переходите на упомянутые страницы, чтобы получить справку от специалистов и узнать что-то новое, что хотя бы минимально или даже значительно ускорит рабочий процесс.
Вставка функции в таблицу
Теперь давайте разберемся с тем, как в Excel задать формулу, то есть добавить ее в таблицу, обеспечив вычисление определенных значений. Вы можете писать функции как самостоятельно, объявляя их название после знака «=», так и использовать графическое меню, переход к которому осуществляется так, как это было показано выше. В Комьюнити уже есть статья «Как вставить формулу в Excel», поэтому я рекомендую нажать по выделенной ссылке и перейти к прочтению полезного материала.
Использование математических операций в Excel
Если необходимо выполнить математические действия с ячейками или конкретными числами, в Excel тоже создается формула, поскольку все записи, начинающиеся с «=» в ячейке, считаются функциями. Все знаки для математических операций являются стандартными, то есть «*»– умножить, «/» – разделить и так далее. Следует отметить, что для возведения в степень используется знак «^». Вкратце рассмотрим объявление подобных функций.
Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.
После заполнения данных нажмите Enter и ознакомьтесь с результатом. Если синтаксис функции соблюден, в выбранной ячейке появится число, а не уведомление об ошибке.
Попробуйте самостоятельно использовать разные математические операции, добавляя скобки, чередуя цифры и ячейки, чтобы быстрее разобраться со всеми возможностями математических операций и в будущем применять их, когда это понадобится.
Растягивание функций и обозначение константы
Работа с формулами в Эксель подразумевает и выполнение более сложных действий, связанных с заполнением строк всей таблицы и связыванием нескольких разных значений. В этом разделе статьи я объединю сразу две разных темы, поскольку они тесно связаны между собой и обе упрощают взаимодействие с открытым в программе проектом.
Для начала остановимся на растягивании функции. Для этого вам необходимо ввести ее в одной ячейке и убедиться в получении корректного результата. Затем зажмите точку в правом нижнем углу ячейки и проведите вниз.
В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.
Константа, или абсолютная ссылка, – обозначение, закрепляющее конкретную ячейку, столбец или строку, чтобы при растягивании функции выбранное значение не заменялось, а оставалось таким же.
Сначала разберемся с тем, как задать константу. В качестве примера сделаем постоянной и строку, и столбец, то есть закрепим ячейку. Для этого поставьте знак «$» как возле буквы, так и цифры ячейки, чтобы в результате получилось такое написание, как показано на следующем изображении.
Растяните функцию и обратите внимание на то, что постоянное значение таким же и осталось, то есть произошла замена только первого аргумента. Сейчас это может показаться сложным, но стоит вам самостоятельно реализовать подобную задачу, как все станет предельно ясно, и в будущем вы вспомните, что для выполнения конкретных задач можно использовать подобную хитрость.
В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:
$В$2 – при растяжении либо копировании остаются постоянными столбец и строка.
B$2 – неизменна строка.
$B2 – константа касается только столбца.
Построение графиков функций
Графики функций – тема, косвенно связанная с использованием формул в Excel, поскольку подразумевает не добавление их в таблицу, а непосредственное составление таблицы по формуле, чтобы затем сформировать из нее диаграмму либо линейный график. Сейчас детально останавливаться на этой теме не будем, но если она вас интересует, перейдите по ссылке ниже для прочтения другой моей статьи по этой теме.
В этой статье вы узнали, какие есть функции в Excel, как сделать формулу и использовать полезные возможности программы, делающие процесс взаимодействия с электронными таблицами проще. Применяйте полученные знания для самостоятельной практики и поставленных задач, требующих проведения расчетов и их автоматизации.
Читайте также: