Excel отменить вычисляемый столбец
Как удалить вычисляемое поле из сводной таблицы?
Если вы добавили несколько новых вычисляемых полей в сводную таблицу как Как добавить вычисляемое поле в сводную таблицу статья упоминается. И после просмотра анализа данных нового пользовательского вычисления в сводной таблице вы хотите удалить вычисляемое поле, чтобы восстановить сводную таблицу. Сегодня я расскажу о том, как временно или навсегда удалить вычисляемое поле из сводной таблицы.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Временно удалить вычисляемое поле из сводной таблицы
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вы хотите временно удалить вычисленное поле, а затем применить его снова, вам просто нужно скрыть поле в списке полей.
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Список полей сводной таблицы панель.
2. В Список полей сводной таблицы панели, пожалуйста, снимите флажок с вычисляемого поля, которое вы создали, см. снимок экрана:
3. После снятия флажка настраиваемого вычисляемого поля это поле будет удалено из сводной таблицы.
Внимание: Если вам нужно, чтобы появилось вычисляемое поле, просто проверьте поле еще раз в Список полей сводной таблицы панель.
Удалить вычисляемое поле из сводной таблицы навсегда
Чтобы окончательно удалить вычисляемое поле, выполните следующие действия:
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.
2. Затем нажмите Доступные опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:
3. В Вставить вычисляемое поле диалоговом окне выберите имя настраиваемого вычисляемого поля из Имя и фамилия выпадающий список, см. снимок экрана:
4. Затем нажмите Удалить кнопку и нажмите OK чтобы закрыть это диалоговое окно, и теперь ваше вычисляемое поле было удалено навсегда, вы больше не сможете его восстанавливать.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Вычисляемые столбцы в таблицах Excel упрощают ввод формул. Формула, введенная в одну ячейку столбца, автоматически распространяется на его остальную часть. При этом не приходится использовать заполнение или копирование. Это позволяет сэкономить время, особенно при наличии множества строк. То же самое происходит при изменении формулы: изменение также распространяется на все остальные ячейки вычисляемого столбца.
Примечание: Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Создание вычисляемого столбца
Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.
Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя "Итог" в ячейке D1.
Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.
Введите нужную формулу и нажмите клавишу ВВОД.
В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.
Дополнительные сведения о структурированных ссылках см. в статье Использование структурированных ссылок в таблицах Excel.
При нажатии клавиши ВВОД формула будет автоматически применена ко всем ячейкам столбца, которые находятся сверху и снизу от активной ячейки. Для каждой строки используется одна и та же формула, но поскольку это структурированная ссылка, Excel знает, на что она ссылается в каждой строке.
При копировании формулы во все ячейки пустого столбца или заполнении его формулой он также становится вычисляемым.
Если ввести или переместить формулу в столбец, уже содержащий данные, это не приведет к автоматическому созданию вычисляемого столбца. Однако отобразится кнопка Параметры автозамены, с помощью которой можно перезаписать данные и создать вычисляемый столбец.
При вводе новой формулы, которая отличается от существующих в вычисляемом столбце, она будет автоматически применена к столбцу. Вы можете отменить обновление и оставить только одну новую формулу, используя кнопку Параметры автозамены. Обычно не рекомендуется этого делать, так как столбец может прекратить автоматически обновляться из-за того, что при добавлении новых строк будет неясно, какую формулу нужно к ним применять.
Если вы введили или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемого столбца, нажмите кнопку Отменить два раза. Вы также можете дважды нажать клавиши CTRL+Z.
В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца. Ячейки с такими формулами становятся исключениями и выделяются в таблице. Это позволяет выявлять и устранять несоответствия, возникшие по ошибке.
Примечание: Исключения вычисляемого столбца возникают в результате следующих операций.
При вводе в ячейку вычисляемого столбца данных, отличных от формулы.
Ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить на панели быстрого доступа.
Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
При копировании в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.
Примечание: Если скопированные данные содержат формулу, она заменяет данные в вычисляемом столбце.
При удалении формулы из одной или нескольких ячеек вычисляемого столбца.
Примечание: В этом случае исключение не помечается.
При удалении или перемещении ячейки в другую область листа, на которую ссылается одна из строк вычисляемого столбца.
Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.
Параметр автоматического заполнения формул для создания вычисляемых столбцов в таблице Excel по умолчанию включен. Если не нужно, чтобы приложение Excel создавало вычисляемые столбцы при вводе формул в столбцы таблицы, можно выключить параметр заполнения формул. Если вы не хотите выключать этот параметр, но не всегда при работе с таблицей хотите создавать вычисляемые столбцы, в этом случае можно прекратить автоматическое создание вычисляемых столбцов.
Включение и выключение вычисляемых столбцов
На вкладке Файл нажмите кнопку Параметры.
Выберите категорию Правописание.
В разделе Параметры автозамены нажмите кнопку Параметры автозамены
Откройте вкладку Автоформат при вводе.
В разделе Автоматически в ходе работы установите или снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы включить или выключить этот параметр.
Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.
Прекращение автоматического создания вычисляемых столбцов
После ввода в столбец таблицы первой формулы нажмите отобразившуюся кнопку Параметры автозамены, а затем выберите Не создавать вычисляемые столбцы автоматически.
Вы также можете создавать настраиваемые вычисляемые поля со с помощью стеблей, в которых создается одна формула Excel а затем применяется ко всему столбце. Подробнее о вычислении значений в pivotTable.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Применимо к: SQL Server Analysis Services Azure Analysis Services Power BI Premium
Вычисляемые столбцы в табличных моделях позволяют добавлять новые данные в модель. Вместо того чтобы вставлять или импортировать значения в столбец, необходимо создать формулу DAX, определяющую значения на уровне строк столбца. Вычисляемый столбец впоследствии может использоваться в отчете, сводной таблице или сводной диаграмме, как и любой другой столбец.
Преимущества
Формулы в вычисляемых столбцах очень похожи на формулы, применяемые в Excel. Однако в отличие от Excel невозможно создать разные формулы для разных строк таблицы, вместо этого формула DAX автоматически применяется ко всему столбцу.
Если столбец содержит формулу, значение вычисляется для каждой строки. Результаты вычисляются для столбца, как только будет введена допустимая формула. Значения столбца затем повторно вычисляются по мере необходимости, например при обновлении базовых данных.
Можно создавать вычисляемые столбцы на основе мер и других вычисляемых столбцов. Например, можно создать один вычисляемый столбец для извлечения номера из текстовой строки, а затем использовать это число в другом вычисляемом столбце.
Вычисляемый столбец создается на основе данных, которые уже присутствуют в существующей таблице, или создается с помощью формулы DAX. Например, можно выполнять объединение, сложение, извлечение подстрок и сравнение значений в других полях. Для добавления вычисляемого столбца в модели должна быть хотя бы одна таблица.
В этом примере демонстрируется простая формула в вычисляемом столбце:
Эта формула извлекает месяц из столбца StartDate. Затем для каждой строки в таблице вычисляется значение конца месяца. Второй параметр задает число месяцев до или после месяца в дате StartDate. В этом случае 0 означает тот же самый месяц. Например, если столбец StartDate имеет 6/1/2001, то значение в вычисляемом столбце будет также 6/30/2001.
Naming a calculated column
По умолчанию новый вычисляемый столбец добавляется справа от других столбцов в таблице, и столбцу автоматически присваивается имя по умолчанию: CalculatedColumn1, CalculatedColumn2и т. д. Чтобы создать новый столбец между двумя существующими, можно также щелкнуть столбец правой кнопкой мыши и выбрать команду «Вставить столбец». Столбцы в одной таблице можно переупорядочить перетаскиванием, а также переименовать после создания, однако необходимо учитывать следующие ограничения на внесение изменений в вычисляемые столбцы.
Имя каждого столбца должно быть уникальным в пределах таблицы.
Не следует использовать имена, которые уже использовались для мер внутри одной модели. Допускается наличие одинаковых имен у меры и вычисляемого столбца, но если они будут неуникальными, то возможно появление ошибок при вычислениях. Чтобы исключить случайный вызов меры при обращении к столбцу, всегда используйте полную ссылку на столбец.
Если меняется имя вычисляемого столбца, необходимо также вручную обновить все зависящие от него формулы. Обновление результатов формул происходит автоматически, если не включен режим ручного обновления. Однако эта операция может занять некоторое время.
Некоторые символы нельзя использовать в именах столбцов. Дополнительные сведения см. в статье "Требования к именованию" справочника по синтаксису DAX.
Performance of calculated columns
Формула для вычисляемого столбца может потреблять больше ресурсов, чем формулы, используемые для мер. Одна из причин этого заключается в том, что результат вычисляемого столбца всегда вычисляется для каждой строки таблицы, а мера вычисляется только для ячеек, указанных в фильтре отчета, сводной таблице или сводной диаграмме. Например, вычисляемый столбец в таблице из миллиона строк всегда будет содержать миллион строк, что соответствующим образом отразится на производительности. Однако в сводной таблице обычно производится фильтрация данных, применяются заголовки строк и столбцы, поэтому мера вычисляется только для подмножества данных в каждой ячейке сводной таблицы.
Формула имеет зависимости от объектов, на которые в ней существуют ссылки, например от других столбцов и выражений, вычисляющих значения. Например, вычисляемый столбец, основанный на другом столбце, или вычисление, содержащее выражение со ссылкой на столбец, не могут быть вычислены до тех пор, пока не будет вычислен этот столбец. По умолчанию автоматическое обновление в книгах включено, поэтому все такие зависимости могут влиять на производительность при обновлении значений и формул.
Чтобы избежать проблем с производительностью, при создании вычисляемых столбцов необходимо придерживаться следующих рекомендаций.
Вместо того чтобы включать в одну формулу множество сложных зависимостей, создавайте формулы последовательно, сохраняя результаты в столбцах. Это позволит проверить результаты и оценить производительность.
Изменение данных часто приводит к необходимости повторного вычисления вычисляемых столбцов. Это можно изменить, выбрав режим повторного вычисления вручную. Но при этом в том случае, если какие-либо значения в вычисляемом столбце окажутся неверными, столбец будет выделен серым и станет неактивным до того момента, пока данные не будут обновлены и повторно рассчитаны.
Если изменить или удалить связи между таблицами, то формулы, в которых используются столбцы из этих таблиц, могут стать неверными.
При создании формулы, содержащей циклическую зависимость или зависимость со ссылкой на себя, возникнет ошибка.
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.
Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl + минус - и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу - как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Как очистить ограниченные значения в ячейках в Excel?
Вы когда-нибудь сталкивались с окном подсказки, как показано на скриншоте слева, при попытке ввести содержимое в ячейку? Это потому, что ячейка была ограничена для ввода определенного значения. В этой статье будет показано, как удалить ограниченные значения из ячеек в Excel.
Quickly prevent duplicate entries in a column in Excel:
The Prevent Duplicate utility of Kutools for Excel help you quickly prevent duplicate entries in a column in Excel. You just need to select the column or a column range and then apply the utility. See screenshot:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Очистить ограниченные значения в ячейках в Excel
Чтобы удалить ограниченные значения в ячейках Excel, сделайте следующее.
1. Выберите ячейку, для которой нужно удалить ограниченное значение, затем щелкните Данные > проверка достоверности данных. Смотрите скриншот:
2. В дебюте проверка достоверности данных диалоговое окно, щелкните Очистить все под Настройки и нажмите OK кнопка. Смотрите скриншот:
Теперь вы очистили ограниченное значение выбранной ячейки.
Быстро очистить ограниченные значения в ячейках с помощью Kutools for Excel
Здесь представьте Очистить ограничения проверки данных полезности Kutools for Excel. С помощью этой утилиты вы можете одновременно удалить все ограничения проверки данных для одного или нескольких выбранных диапазонов.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон или несколько диапазонов, в которых вы удалите все ограничения проверки данных. Нажмите Кутулс > Предотвратить ввод > Очистить ограничения проверки данных.
2. Во всплывающем Kutools for Excel диалоговое окно, нажмите OK чтобы начать снимать ограничения проверки данных.
Затем все ограничения проверки данных удаляются из выбранного диапазона (ов).
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: