« Trucs Excel » : différence entre les versions

De FrozenWiki
Aller à la navigation Aller à la recherche
Aucun résumé des modifications
Aucun résumé des modifications
Ligne 2 : Ligne 2 :
== Afficher/Cacher les valeurs ZERO dans Excel ==
== Afficher/Cacher les valeurs ZERO dans Excel ==


'''Display or hide all zero values on a worksheet'''
=== Display or hide all zero values on a worksheet ===


# On the Tools menu, click Options, and then click the View tab.
# On the Tools menu, click Options, and then click the View tab.
Ligne 9 : Ligne 9 :
#* To display zero values as blank cells, clear the check box.
#* To display zero values as blank cells, clear the check box.


'''Use a number format to hide zero values in selected cells'''
=== Use a number format to hide zero values in selected cells ===


''Caution:'' This topic applies a format to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the General number format.
''Caution:'' This topic applies a format to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the General number format.
Ligne 22 : Ligne 22 :
* To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.
* To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.


'''Use a conditional format to hide zero values returned by a formula'''
=== Use a conditional format to hide zero values returned by a formula ===


# Select the cell that contains the zero value.
# Select the cell that contains the zero value.
Ligne 32 : Ligne 32 :
# In the Color box, select white.
# In the Color box, select white.


'''Use a formula to display zeros as a blanks, or dashes'''
=== Use a formula to display zeros as a blanks, or dashes ===


Use the IF function to do this task.
Use the IF function to do this task.


''Worksheet example''
'''Worksheet example'''


The example may be easier to understand if you copy it to a blank worksheet.
The example may be easier to understand if you copy it to a blank worksheet.


''How?''
'''How?'''


# Create a blank workbook or worksheet.
# Create a blank workbook or worksheet.
Ligne 48 : Ligne 48 :
# To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
# To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.


''Formula / Description (Result)''
'''Formula / Description (Result)'''


* =A2-A3 / Second number subtracted from the first (0)
* =A2-A3 / Second number subtracted from the first (0)
Ligne 54 : Ligne 54 :
* =IF(A2-A3=0,"-",A2-A3) / Returns a dash when the value is zero (-)
* =IF(A2-A3=0,"-",A2-A3) / Returns a dash when the value is zero (-)


'''Hide zero values in a PivotTable report'''
=== Hide zero values in a PivotTable report ===


# Click the report.
# Click the report.

Version du 9 décembre 2004 à 16:56

Afficher/Cacher les valeurs ZERO dans Excel

Display or hide all zero values on a worksheet

  1. On the Tools menu, click Options, and then click the View tab.
  2. Do one of the following:
    • To display zero (0) values in cells, select the Zero values check box.
    • To display zero values as blank cells, clear the check box.

Use a number format to hide zero values in selected cells

Caution: This topic applies a format to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the General number format.

  1. Select the cells that contain the zeros (0's) you want to hide.
  2. On the Format menu, click Cells, and then click the Number tab.
  3. In the Category list, click Custom.
  4. In the Type box, type 0;-0;;@

Notes:

  • The hidden values appear only in the formula bar — or in the cell if you edit within the cell— and are not printed.
  • To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Use a conditional format to hide zero values returned by a formula

  1. Select the cell that contains the zero value.
  2. On the Format menu, click Conditional Formatting.
  3. In the box on the left, click Cell Value Is.
  4. In the second box from the left, click equal to.
  5. In the box on the right, type 0.
  6. Click Format, and then click the Font tab.
  7. In the Color box, select white.

Use a formula to display zeros as a blanks, or dashes

Use the IF function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.
  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

Formula / Description (Result)

  • =A2-A3 / Second number subtracted from the first (0)
  • =IF(A2-A3=0,"",A2-A3) / Returns a blank cell when the value is zero (blank cell)
  • =IF(A2-A3=0,"-",A2-A3) / Returns a dash when the value is zero (-)

Hide zero values in a PivotTable report

  1. Click the report.
  2. On the PivotTable toolbar, click PivotTable, and then click Table Options.
  3. Do one or more of the following:
    • Change error display: Select the For error values, show check box under Format options. In the box, type the value you want to display instead of errors. To display errors as blank cells, delete any characters in the box.
    • Change empty cell display: Select the For empty cells, show check box. In the box, type the value you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.


Réduire la taille d'un fichier

Unfortunately it is not too uncommon to have an Excel Workbook increase in file size for no apparent reason. Below are some methods to overcome this problem, that should be performed in the order stated! Please note OzGrid accepts no responsibility for any loss of data. This means save a copy of your Workbook first. After each step Save and check the File size by going to File>Properties/General If it has reduced the file size you shouldn't need to go any further!

Avoid saving as multiple versions. Save as "Microsoft Excel Workbook (*.xls). You can make this the default by going to Tools>Options/Transition and selecting Microsoft Excel Workbook from the Save Excel files as Combobox. This alone can reduce file size by 50%

Do the following to ALL Worksheets. Select any single cell then push F5, click Special and check the "Blanks" option and click Ok. Now go to Edit>Clear>All. Manually go to the last used row in the Worksheet (do not use Edit>Go to-Special "Last Cell"). Select the entire row beneath, then holding down the Ctrl+Shift key push the Down Arrow. This should select all unused rows. Now go to Edit>Clear>All. Do the same for the Columns. Now Save.

Right click on any sheet name tab and select "Select all sheets". Now right click again and select "Move or copy" Check the Create a copy option then choose "(New Book)" and click Ok. Save this new Workbook as any name and check it's file size via File>Properties/General.

If no dramatic difference delete one sheet at a time and Save, each time check it's file size via File>Properties/General and see if there has been a disproportional reduction in file size.

If there has, the Worksheet may have been corrupt, go back the the original Workbook and copy the entire contents of the possibly corrupt Worksheet. Come back to the new Workbook, insert a new Worksheet and paste the data onto it. If this does not help you may need to delete a few rows at the time (Saving each time) until you find the offending row(s).

If you have Modules and/or UserForms in the Workbook, open the VBE (Alt+F11) and right click on each in the "Project Explorer" (Ctrl+R) and Export. Now, in a new Workbook again open the VBE and go to File>Import File.. and import all Modules and UserForms into this Workbook. Use the Move or copy method (as shown in step above) to copy all sheets into this Workbook Save.