在Microsoft Excel中使用电子表格的好处之一是能够重用公式并将其应用于新情况。在这些情况下,您要确保不会错误地复制任何可能扭曲和污染结果的旧条目值。当您从其他人那里收到电子表格时,或者甚至在您处理包含相当复杂性的电子表格时,您希望快速隔离并删除所有输入您使用的公式的输入值。有一种方法可以快速删除Excel 电子表格(Excel spreadsheet)中的所有值并只保留公式,而不是逐个单元格地验证电子表格。(spreadsheet cell)让我们看看这是如何工作的:
注意(NOTE):本教程适用于Microsoft Office和Office 365中的(Office 365)Microsoft Excel桌面版本。它不适用于Excel的移动版本,例如那些在Windows 10平板电脑或Android 和 iOS(Android and iOS)设备上免费提供的版本。
如何从单元格中删除值但将公式保留在Microsoft Excel 中(Microsoft Excel)
我们以Microsoft提供的贷款摊销时间表(loan amortization schedule)作为其Office 模板的一部分,可供下载(Office templates available for download)。我们想重用这个电子表格并建立我们自己的模拟。为此,我们希望确保电子表格中没有任何值来自Microsoft提供的先前数据。
首先,确保功能区上的活动选项卡是Home。查找“编辑(Editing)”部分,并在其中找到“查找和选择(Find & Select)”下拉菜单。点击(Click)它。在菜单中选择(menu select)“转到特别...(Go To Special…) ”。
在出现的弹出窗口(pop-up window)中,选择Constants,然后在Formulas下,确保仅选中Numbers复选框。
完成后,单击或点击OK。
Microsoft Excel将分析您的电子表格并自动选择所有仅包含数字(numbers only)的单元格,而未选择包含公式或文本的单元格。
对于下一步,请确保您不要单击或点击电子表格中的任何位置,因为这样做会取消您当前的选择。在主页(Home)选项卡的同一编辑(Editing)部分中,单击或点击清除(Clear)按钮。该按钮由橡皮擦表示,如果应用程序窗口(application window)中有足够的空间,还会出现“清除(Clear)”一词。在下拉菜单中,选择清除内容(Clear Contents)。或者,使用键盘并按(keyboard and press)Delete。
电子表格现在已清除所有输入数字,同时保留所有公式并准备好用于您的下一个电子表格。您现在可以确定,与这些公式一起使用的任何数字都是您新输入的,并且没有过去的剩余数字。
您(Did)是否设法清理电子表格?
阅读本指南后,您会发现清理旧输入值的电子表格是多么容易,并且可以快速而准确地节省时间。通过在下面发表评论(comment below),让我们知道这对您有何帮助。此外,您是否希望我们在教程中回答任何与 Excel 相关的问题?
How to delete values from cells but keep your formulas, in Microsoft Excel
One of the blessings of working with spreadsheets in Microsoft Excel is the аbility to reuse the formulas and аpply them to new situations. In those cases, yoυ want to make sure уou do not copy bу mistаke any of the old entry values that сould distort and taint the outcome. When you receive a spreadsheet from ѕomeone else or even when you work on one of yoυrs that contains a fair bit of complexitу, yoυ want to quickly isolate and delete all the input values that feеd the formulas that yоu use. Instеad of vеrifying the spreadsheet cell by cell, doing grueling work, there is a method to quickly delete all values from an Excеl sрreadsheet and keep only the formulas. Let's see how this works:
NOTE: This tutorial applies to the desktop versions of Microsoft Excel, found in Microsoft Office, and Office 365. It does not apply to the mobile versions of Excel, like those found for free, on Windows 10 tablets, or on devices with Android and iOS.
How to delete values from cells but keep your formulas in Microsoft Excel
We use as an example the loan amortization schedule provided by Microsoft as part of their Office templates available for download. We want to reuse this spreadsheet and build our own simulation. To achieve that, we want to make sure that no values remain in the spreadsheet from the previous data provided by Microsoft.
First, make sure the active tab on the ribbon is Home. Look for the Editing section and in there locate the Find & Select drop-down menu. Click on it. In the menu select "Go To Special…."
In the pop-up window that appears, select Constants, and then, under Formulas, make sure only the Numbers checkbox is marked.
When you finish, click or tap OK.
Microsoft Excel is going to analyze your spreadsheet and automatically select all the cells that contain numbers only, leaving unselected the cells containing formulas or text.
For the next step, make sure you do not click or tap anywhere in the spreadsheet because doing so it cancels your current selection. In the same Editing section of the Home tab, click or tap the Clear button. The button is represented by an eraser and, if there is enough space in the application window, the word Clear also appears. In the drop-down menu, select Clear Contents. Alternatively, use the keyboard and press Delete.
The spreadsheet is now cleaned of all input numbers while all the formulas are preserved and ready to be used for your next spreadsheet. You can be sure now that any numbers that are used with these formulas are entered fresh by you and there are no leftovers from the past.
Did you manage to clean your spreadsheet?
After you go through this guide, you find how easy it is to clean a spreadsheet of old input values and save time doing this quickly and precisely. Let us know how this worked for you by leaving a comment below. Also, are there any Excel-related questions that you want us to answer in our tutorials?