Итак, у меня есть электронная таблица, используемая для расчета, скажем, налогов на что-то, что я купил. В ней есть столбец с расчетом налога с продаж, налога штата и т. д., и все эти столбцы ссылаются на значение первого столбца (цена того, что я купил). У меня заполнена первая строка со всеми выполненными относительными ссылками. Теперь, когда я добавляю цену во вторую строку, ни один из этих столбцов не заполняется автоматически. Мне приходится вручную дважды щелкать по каждому из 100 других столбцов, чтобы он заполнился. Я попробовал добавить первый столбец только с числами от 1 до 1000, чтобы он обновлял все, когда я добавляю новую цену, но тогда электронная таблица выглядит как полный беспорядок с нулями, отрицательными числами, ошибками и т. д. повсюду. Я хочу иметь возможность просто добавить это одно число «цена» в новый столбец строки A, и все остальное заполнялось автоматически каждый раз, когда я что-то добавляю сюда. Как мне этого добиться?
Примечание: Я уже просмотрелПрименение формулы к диапазону ячеек без перетаскиваниядля ответа, но они обсуждают другой сценарий.
решение1
Здесь я немного порассуждаю, поэтому смиритесь с моими догадками о том, что именно вы делаете и с чем вы это делаете.
КАЖЕТСЯ, что вы заполнили столбец A чем угодно, 1-1000, просто "пустышкой", удерживая значения шаблона, и все пошло наперекосяк. Это явно подразумевает, что у вас есть формулы во всех других ячейках справа от того, что вы заполнили, отсюда и все уродливое. Так что мой первый ход здесь будет разобраться с этим и показать вам трюк, чтобы все это было красиво-красиво.
Когда вы берете целую формулу, простую или сложную, которая делает что-то, что вы хотите, но не справляется со всем этим, чтобы выглядеть красиво, или с чем-то вроде "ну, время от времени...", это называется "оборачивание формулы" какой-то новой функцией. Простой пример: вы делаете что-то, что в результате получается строкой, но в начале или конце есть неудачные пробелы, или несколько пробелов вместе в середине. Вы "оборачиваете" все до этого функцией, TRIM()чтобы она удалила эти раздражающие пробелы.
В этом случае у вас есть формулы, но вы не хотите, чтобы их беспорядок отображался, если только в столбце A нет реального значения. Поэтому оберните их (это нужно сделать только в первой строке, скопируйте и вставьте так далеко, как вы считаете нужным, чтобы вы могли добавлять данные без дополнительной работы в течение приличного времени. Может быть, сто строк? Может быть, тысячу?) следующей IF()функцией:
=IF(A4="","", (what you had goes here) )
Итак, все ячейки в строке 4 будут проверять, есть ли запись в A4. Если нет, они все возвращают "" (ничего, пустая ячейка). Когда в A4 что-то есть, они показывают результат рабочей формулы, все выглядит красиво и все такое.
Однако есть нечто под названием "Таблица", которая была придумана ТОЧНО для решения вашей проблемы. Вам придется выделить и Deleteвсе формулы в тех ячейках, которые вы еще не используете (включая фиктивные данные в столбце A), оставив только формулы там, где есть реальные данные столбца A. Все строки ниже очищены с помощью Delete.
Затем выберите любую ячейку В реальных данных, которые существуют, или выделите их все, включая заголовки, затем перейдите к вашему Ribbonи нажмите на INSERT. Первый раздел — это Tablesраздел, а самый правый значок там, третий слева, — «Таблица» — нажмите на него. Это откроет небольшое (действительно маленькое) диалоговое окно с двумя вещами в нем. Во-первых, Excel догадывается, какие именно ячейки составляют вашу таблицу. Обычно это довольно точно, если только у вас нет вещей, приставленных к этим ячейкам, которые могут запутать его. Если все выглядит правильно, продолжайте. Если нет, используйте мышь, чтобы выделить все, включая ваши заголовки, чтобы Excel не мог ошибиться. Затем, если у вас ЕСТЬ заголовки, щелкните, чтобы заполнить поле под ним с помощью «Моя таблица имеет заголовки», затем щелкните, OKи все готово.
Теперь, когда вы вводите что-то в ячейку столбца A строки, остальная часть строки автоматически заполняется формулами. Вот почему вам пришлось избавиться от всей этой ерунды, которая вам не нравилась. Вы вводите что-то в ячейку A, и все остальное мгновенно заполняется. Здорово. Некоторые мелочи... Я имею в виду следующую строку. Не 40 пустых строк, а затем вы заполняете ячейку A 41-й. Это обречено на провал... Кроме того, если ваша формула должна измениться по какой-либо причине, это кошмар. Вы измените формулу ячейки, а затем увидите, как старая формула заполняется, когда вы используете следующую строку. Сложно, как это исправить. Одна из мыслей о вашем заявленном использовании здесь была бы, если вы используете это со временем, налоговые ставки могут измениться. Предложение было бы сделать небольшие таблицы поиска налоговых ставок в другом месте, и в формуле каждой ячейки использовать функцию поиска, например XLOOKUP()или VLOOKUP(), чтобы искать их на основе даты (может быть, нужен столбец для дат, а?). Таким образом, вы можете изменять или дополнять эти маленькие таблицы, и формула в них Tableне изменится.
Третье лучшее — копировать строку формул вниз каждый раз, когда вам нужна новая строка. Это можно сделать несколькими способами. Помимо очевидных (выделение, копирование, вставка, верно?), вы можете заполнять каждый раз, когда вам нужно, просто выделяйте, как будто собираетесь копировать и вставлять, а затем используйте инструмент заполнения. Вы даже можете сделать что-то эзотерическое, например, определить именованный диапазон с помощью относительной ссылки, которая является строкой, на которой вы находитесь, за исключением ячейки A в этой строке, а затем каждый раз, когда вам нужна новая строка, выбирать ячейку в используемой строке, затем использовать поле диапазона над A1, слева от панели редактирования F2, чтобы показать и выбрать этот диапазон, дважды щелкнув его в раскрывающемся поле. Это выделит текущую строку, и вы сможете скопировать, затем перейти к пустой строке и вставить. Знаете, так вы можете сэкономить ужасные усилия по выделению строки формул, потому что это сделает это за вас.
Суть в том, что это не самый веселый из вариантов. Так что используйте один из первых двух, ОСОБЕННО конвертацию в Table. Tablesесть и другие преимущества, хотя ни одно из них не будет выглядеть так, как будто они имеют значение для вашего использования здесь.
СТОЛЫ... Я не их самый большой поклонник, но это невероятно точно то, что вам нужно здесь, и так легко настроить. О, просто примите любой стиль, который он захочет сделать. Измените его позже, если захотите. Ходите перед тем, как бегать, а?