ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1550
Скачиваний: 3
Расширенные возможности Microsoft Excel 2007
21
Форматы для числовых данных
Полный формат
состоит из
4
секций
, разделенных знаком
;
(точка с запятой)
поле
Тип
: ПОЛОЖИТЕЛЬНАЯ
;
ОТРИЦАТЕЛЬНАЯ
;
НУЛЕВАЯ
;
ТЕКСТОВАЯ
Если формат содержит
1
секцию
–
он применяется ко всем числам
(
<0
,
0
,
>0
)
Формат из
2
х
секций
–
1
я
применяется к
нулю
и числам
>0
,
2
я
к числам
<0
Если
отсутствует
4
я
секция
–
формат
не оказывает влияние на текст
.
Каждая из
4
х секций
может содержать следующие элементы
:
[
ЦВЕТ
]
«
ТЕКСТ
В КАВЫЧКАХ
»
КОДЫ
ФОРМАТА
«
ТЕКСТ
В КАВЫЧКАХ
»
1.
2.
3.
4.
Список цветов
:
[
Черный
]
–
[Black]
[
Синий
]
–
[Blue]
[
Голубой
]
–
[Cyan]
[
Зеленый
]
–
[Green]
[
Фиолетовый
]
–
[Magenta]
[
Красный
]
–
[Red]
[
Белый
]
–
[White]
[
Желтый
]
–
[Yellow]
В
КОДАХ
ФОРМАТА
используют
знаки
:
Знак
Назначение
#
Выводит
только значащие числа
, незначащие нули не отображаются
0
(
нуль
)
Незначащие нули отображаются
, если длина числа меньше длины формата
Пробел
Разделитель разрядов
или
в конце кода числа
– отображение
числа
в масштабе
: тысяч (1 пробел), миллионов (2 пробела) и т.д.
Запятая Разделитель целой и дробной
части
@
Для текста
. Включить
в текстовую секцию
, чтобы отображать вводимый в
ячейку текст. Если
@
отсутствует, то вводимый текст не будет отображаться.
Примеры
:
Для отображения
Используйте шаблон
1234,59
в виде
1234,6
####.#
8,9
в виде
8,900
#,000
0,631
в виде
0,6
0,#
12
в виде
12,0
и
1234,568
в виде
1234,57
#,0#
12000
в виде
12
000
# ###
12000
в виде
12
# (пробел)
12200000
в виде
12,2
0,0 (два пробела)
12
в виде
00000012
00000000
1200
в виде
1
200
у
.
е
.
# ##0” у.е.”
1
в виде
1.
(
используется для нумерации
)
#”.”
Пример сложного формата со всеми группами
:
[
синий
]
”
Доход
”
#
##0,00
”
у
.
е
.
”
;
[
красный
]
”
Убыток
”
#
##0,00
”
у
.
е
.
”
;
[
зеленый
]
”
В расчете
”
;
[
желтый
]
”
Н
/
Д
”
положительные
отрицательные
нулевые
текстовые
Этот формат отображает
:
Введенное в ячейку
(число/текст)
Отображается в ячейке
Цвет отображения
12345,67
(
положительное число
)
Доход 12 345,67 у.е.
синий
145678,98
(
отрицательное число
)
Убыток 145 678,98 у.е.
красный
0
(
нуль
)
В расчете
зеленый
Текст
(
любой текст
)
Н/Д
желтый
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
22
Условное форматирование
Условное форматирование – оформление ячеек в зависимости от условий.
К одному блоку данных может быть применено несколько разных схем оформления:
Установка условного форматирования ячеек
1.
Выделить
ячейки листа для форматирования;
2.
Перейти на вкладку
Главная
(Home);
3.
В группе
Стили
(Styles), раскрыть кнопку
Условное форматирование
(Conditional Formatting), выбрать:
a.
Правила выделения ячеек
(Highlight Cells Rules) – выделение ячеек
(
цветом заливки
,
границы
,
шрифта
,
начертанием
,
числовым форматом
)
по выбранному критерию:
Больше
,
Меньше
,
Между
,
Равно
,
Содержащие
текст
,
Даты
(вчера, сегодня, завтра, прошлая неделя, прошлый месяц…),
ячейки
с одинаковым
или
уникальным
значением
,
дополнительный
выбор: не равно, не содержит, заканчивается…
b.
Правила отбора первых и последних
значений (Top/Bottom
Rules) – форматирование указанного числа крайних значений:
N (N
%
)
наибольших
,
наименьших
элементов,
Больших
,
Меньших среднего
арифметического;
c.
Гистограммы
(Data Bars) – градиентная заливка ячейки, длина
которой напрямую зависит от числа, находящегося в ней;
d.
Цветовые шкалы
(Color Scales) – заливка в 2 или 3 цвета, которая
напрямую зависит от числа, находящегося в ячейке;
e.
Наборы значков
(Icon Sets) – установка значка, отражающего
тенденцию изменения чисел в ячейках.
Расширенные возможности Microsoft Excel 2007
23
Редактирование условного форматирования
1.
Вкладка
Главная
(Home), в группе
Стили
(Styles) раскрыть кнопку
Условное форматирование
(Conditional Formatting);
2.
Выбрать команду
Управление правилами
(Rules Manager) – окно диалога,
где можно просматривать, удалять, редактировать все условные
форматирования диапазона, листа, книги.
Создание правила с использованием формулы
1.
Выделить
ячейки для оформления;
2.
Из кнопки
Условное форматирование
,
выбрать
Создать правило
(New Rule);
3.
В
появившемся
окне
выбрать:
Использовать формулу для
определения
форматируемых
ячеек
(Use a formula to
determine which cells…);
4.
Ввести формулу
, выбрать оформление из
кнопки
Формат
(Format), нажать
OK
.
Пример
1.
Выделить цветом максимальное число
Выделим числа (пусть С1:С10),
Условное форматирование
,
Создать правило
,
Использовать формулу
…
введем:
=
С
1=
МАКС
(
$
С
$1:$
С
$10
)
,
Формат
,
OK.
Здесь
C1
–
1
я
ячейка оформляемого диапазона С
1:
С
10;
C1
должна меняться по
строкам на
C2,
C3…
C10,
поэтому адресация
без фиксации строки
:
C1
или
$C1
.
МАКС
(
$
С
$1:$
С
$10
)
–
максимальное
число
,
одинаково для
всех ячеек С
1:
С
10;
диапазон
не должен меняться
при переходе от С
1
к С
2…,
–
абсолютная
ссылка
(
$
С
$
1:
$
С
$
10).
Пример
2.
Выделить цветом числа выше среднего
Выделим числа (пусть С1:С10),
Условное форматирование
,
Создать правило
,
Использовать формулу
…
введем:
=
С
1>
СРЗНАЧ
(
$
С
$1:$
С
$10
)
,
Формат
,
OK.
C1
–
1
я
ячейка оформляемого диапазона
,
должна меняться
,
–
относительный
адрес
.
СРЗНАЧ
(
$
С
$1:$
С
$10
)
–
среднее
число
из С
1:
С
10,
диапазон не изменяется
(
$
С
$
1:
$
С
$
10).
В английской версии:
Max
($С$1:$С$10) – максимум,
Average
($С$1:$С$10) – среднее.
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
24
Вычисления
Функции
Обращение к функции
:
ИМЯ
_
ФУНКЦИИ
(
аргумент
1
;
аргумент
2
;
…
)
.
Имя функции не
содержит пробелов
.
Аргументы идут через
;
и их количество зависит от функции
.
Способы вставить функцию
1
способ
.
В
кладка
Формулы
(Formulas) –
f
х
Вставить
функцию
(Insert Function)
;
Функции
разбиты
на
группы
–
категории
.
В окне мастера функций
выберите
из
списка
нужную категорию
,
затем
нужную
функцию
,
OK.
В
появившемся
окне
ввести аргументы
,
OK.
Аргументами могут быть
числа
,
адреса других ячеек
,
вычисляемые выражения и
другие функции
.
2
способ
.
В
кладка
Формулы
(Formulas)
, в группе
Библиотека функций
(Function
Library) выбрать
функцию
из кнопки
категории
, ввести аргументы, OK;
3
способ
.
Ручной ввод
. В ячейке после знака
=
ввести
первые
буквы имени
функции,
в появившейся подсказке
дважды щелкнуть
по подходящей функции
.
Для удобства можно вызвать
окно
Аргументы
функции
(
fx
или
SHIFT
+
F3
)
Для того
,
чтобы в качестве
аргумента
одной
функции
использовать
другую функцию
,
надо
в строке формул
раскрыть
список функций
,
выбрать функцию
из списка
10
ти
последних или
перейти на
Другие функции
…
для выбора любой другой
.
Расширенные возможности Microsoft Excel 2007
25
Математические функции
Функции округления
ОКРУГЛ
(
число
;
число
_
разрядов
)
ROUND
Округляет число до указанного количества десятичных разрядов
(
по правилам матем
.)
ОКРУГЛ(
156,236 ; 2
)
156,24
ОКРУГЛ(
178,891 ;
0
)
179
ОКРУГЛ(
156,236 ; 1
)
156,2
ОКРУГЛ(
178,89 ;
1
)
180
ОКРУГЛВНИЗ
(
число
;
число
_
разрядов
)
ROUNDDOWN
Округляет число до ближайшего
меньшего
по модулю числа с указанным количеством
десятичных разрядов
(
округление всегда в меньшую сторону
)
ОКРУГЛВНИЗ(
156,236
;
2
)
156,23
ОКРУГЛВНИЗ (
178,891
;
0
)
178
ОКРУГЛВНИЗ (
156,236
;
1
)
156,2
ОКРУГЛВНИЗ (
178,89
;
1
)
170
ОКРУГЛВВЕРХ
(
число
;
число
_
разрядов
)
ROUNDUP
Округляет число до ближайшего
большего
по модулю числа с указанным количеством
десятичных разрядов
(
округление всегда в большую сторону
)
ОКРУГЛВВЕРХ(
156,231
;
2
)
156,24
ОКРУГЛВВЕРХ (
178,891
;
0
)
179
ОКРУГЛВВЕРХ (
156,236
;
1
)
156,3
ОКРУГЛВВЕРХ (
178,89
;
1
)
180
ОКРВНИЗ
(
число
;
кратное
_
число
)
FLOOR
Округляет число до числа
кратного данному
,
меньшего
по модулю
ОКРВНИЗ(
156,236
;
2
)
156
ОКРВНИЗ (
178,89
;
15
)
165
ОКРВНИЗ (
156,23 ;
10
)
150
ОКРВНИЗ (
178 ;0
)
#
ДЕЛ
/0!
деление на
0
ОКРВВЕРХ
(
число
;
кратное
_
число
)
CEILING
Округляет число до числа
кратного данному
,
большего
по модулю
ОКРВВЕРХ(
156,236
;
2
)
158
ОКРВВЕРХ (
178,89
;
15
)
180
ОКРВВЕРХ (
156,23
;
10
)
160
ОКРВВЕРХ (
178 ;0
)
#
ДЕЛ
/0!
деление на
0
ОТБР
(
число
;
число
_
разрядов
)
TRUNC
Отбрасывает
дробную
часть числа
до указанного количества разрядов
ОТБР (
156,236
;
2
)
156,23
ОТБР(
178,891 ; 0
)
178
ОТБР (
156,236
;
1
)
150
ОТБР(
178,89 ;
2
)
100
ЦЕЛОЕ
(
число
)
INT
Округляет число до ближайшего
меньшего целого
ЦЕЛОЕ(
156,236
)
156
ЦЕЛОЕ(
178,89
)
178
ЧЕТН
(
число
)
EVEN
Округляет число до ближайшего
большего четного
ЧЕТН(
156,23
)
158
ЧЕТН(
171
)
172
НЕЧЕТ
(
число
)
ODD
Округляет число до ближайшего
большего нечетного
НЕЧЕТ (
156,23
)
158
НЕЧЕТ
(
171
)
172