Задачи для самостоятельного решения. 1. Для чего нужно форматирование данных?

Контрольные вопросы

1. Для чего нужно форматирование данных?

2. Перечислите средства выравнивания данных.

3. Как объединить ячейки?

4. Перечислите параметры шрифтов.

5. Какие следствия возникают в результате применения к числу знака %?

6. Что такое условное форматирование? Сколько и каких секций может быть в условном формате?

7. Как сделать, чтобы в дате отображалось имя месяца и дня недели?

8. Зачем нужен Мастер условного форматирования?

9. Для чего нужна проверка ввода и где находятся эти средства?

10. Перечислите три уровня контроля ввода данных.

11. Как скопировать в таблице только форматы?

Упражнения

1. Введите в клетку С2 слово Петр. Последовательно сделайте его выравнивание к правой и левой границам и к центру клетки. Расположите слово вертикально и под другим углом.

2. Вернитесь к исходному виду клетки С2. Объедините ячейки С2 и D2 и отцентрируйте здесь слово Петр.

3. Установите в клетке шрифт Courier New, жирный, курсив, размером 18 пунктов.

4. Введите в клетку В1 число 2468,637 и скопируйте его в нижние 18 клеток.

В2	2468,63700		В8	0002468,7		В14	3.Октябрь.1906
В3	2 469		В9	*********2 469		В15	3.10
В4	2 468,64р.		В10	2,47E+03		В16	3.10.06
В5	246864%		В11	вес 2469 кг		В17	3.среда.Октябрь
В6	2 468,64 $		В12	2 тыс.		В18	03.10.06 15:17
В7	$ 2468,6		В13	2468 7/11		В19	15:17:17

Установите для них вид, показанный в таблице используя как стандартные, так созданные вами форматы.

5. Создать (через меню Формат+Ячейка+вкладка Число) условный формат, следующим образом реагирующий на данные в зависимости от диапазона, в котором они находятся:

– если число (например, 0,253) меньше 1 – выводятся два дробных разряда со словом грамм (0,25грамм) синего цвета,

– если число (например, 637,58) больше 1, но меньше 1000 – выводится только целая его часть со словом кг (638кг) синего цвета,

– если число (например, 263124,58) больше 1000 – выводятся только тысячи со словом тонн (263тонн),

– если по ошибке в клетку введено символьное значение, например пробел, – выводится слово ОШИБКА красного цвета.

6. С помощью Мастера условного форматирования построить следующий условный формат для любой текущей клетки:

– если число четное (делится без остатка на 2) и больше нуля – его значение выводится жирным шрифтом на красном фоне,

– если число меньше 1 – курсивом на синем фоне.

7. С помощью средств проверки ввода разрешить ввод в клетку только чисел в диапазоне от 10 до 100 000.

8. С помощью средств проверки разрешить ввод в клетку только данных из списка, содержащего наименования подразделений (Кадры, Дирекция, Касса, Связь, Охрана) организации.

Тесты (выберите ответ)

1. В чем измеряется размер символов? – в пикселях, в миллиметрах, в пунктах.

2. По умолчанию текст выравнивается? – к правой границе, к левой границе, по центру.

3. По умолчанию числа выравниваются? – к правой границе, к левой границе, по центру.

 

ФУНКЦИИ РАБОЧЕГО ЛИСТА

Главным инструментом при работе с электронными таблицами являются клеточные функции. Напомним, что ввод адресов и ключевых слов, используемых в функциях, можно осуществлять как заглавными, так и строчными буквами. Если выражение не содержит ошибок, после нажатия клавиши Enter они автоматически преобразуются в заглавные. Ввиду этого, лучше вводить такие данные строчными буквами, что позволит быстро выявить допущенные синтаксические ошибки. Непреобразование их в заглавные будет означать, что соответствующий элемент введен неверно. Напомним также, что при вводе всем функциям должен предшествовать знак “=”. Если функция получается очень длинной, удобно разбивать ее на отдельные строки внутри клетки, используя клавиши Alt+Enter. Некоторую помощь при вводе формул пользователю может оказать Мастер функций (кнопка “ = ” в строке формул) дающий возможность поиска и формирования нуж­ных функций рабочего листа (здесь не рассматривается).

Замечания.

1. Некоторые из перечисленных функций не входят в ядро Excel и становятся доступны только при загрузке дополнительного Пакета анализа из меню Сервис+Надстройки. Все они помечены в тексте двумя галочками.

2. Ниже в качестве аргументов функций применяются термины число, текст и т.д. В подавляющем большинстве случаев в качестве этих значений могут использоваться не только константы соответствующих типов, но и ссылки на ячейки, а часто и блоки ячеек, содержащие данные указанных типов.

3. По принятым в компьютерной литературе соглашениям, квадратные скобки обрамляют необязательные параметры функции.

Функция суммирования

В виду особой важности этой функции, ее рассмотрение вынесено в отдельный раздел.

Функция имеет следующий формат:

üСУММ(число1; число2;...) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов. Если в суммируемом блоке встречаются нечисловые элементы, они учитываются как нули.

Примеры: СУММ(3;2)=3+2=5, СУММ(A1;2;A2:C3)=A1+2+A2+B2+C2+A3+B3+C3.

Выражение, содержащее функцию суммирования, можно ввести с клавиатуры, а можно с помощью пиктограммы Автосуммирование å (см. раздел 2.4) или комбинации клавиш Alt + =.

Функция суммирования аналогична простому перечислению слагаемых со знаком плюс. Следующие выражения в Excel совершенно эквивалентны: =СУММ(A1:A3) и =A1+A2+A3. В каких же случаях следует пользоваться функцией суммирования, а в каких сложением? Функция СУММ() удобна, если слагаемых много и они находятся в смежных ячейках. Очевидно, гораздо проще набрать выражение СУММ(D1:D400), нежели адреса четырехсот слагаемых D1+D2+… + D400. Кроме того, при большом числе элементов это может оказаться невозможным, поскольку длина клеточного выражения ограничена. Имеется еще одно отличие. Если среди слагаемых имеется нечисловой элемент, он будет пропущен функцией СУММ(…), но приведет к ошибке при сложении через знак плюс. Другое удобство заключается в том, что функция автоматически реагирует на изменение (включение/удаление строк в диапазоне суммирования) размеров таблицы. На рис. 4.1-1 приведены результаты суммирования данных из области А1:А2 в числовом и формульном виде с использованием обеих формул. Они одинаковы (результат равен 6).

 

A

B

A

B

A

B

A

B

=A1+A2

=СУММ(A1:A2)

Рис. 4.1-1

Рис. 4.1-2

=A1+A2

=СУММ(A1:A3)

 

На рис. 4.1-2 в диапазон суммирования между первой и второй строками вставлена строка, содержащая цифру 5. Видим, что результат, полученный с помощью функции СУММ(), правильный, поскольку функция автоматически учла новое слагаемое (выражение =СУММ(A1:A2) изменилось на =СУММ(A1:A3)). Формула же =А1+А2 осталась прежней и, как следствие, результат неверный. Такое адаптивное поведение вообще характерно для любых функций Excel – они, в основном, правильно учитывают изменения в структуре таблиц.

Однако здесь имеются два исключения – вставка строки непосредственно над областью обработки (*) и непосредственно над строкой (**), содержащей суммы (рис. 4.1-3).

		A			A
		Заголовок			Заголовок
					вставка*
		=СУММ(A2:A3)
					вставка**
Рис. 4.1-3					=СУММ(A3:A4)

Функция СУММ() не откликается на эти действия, продолжая считать своими аргументами только те клетки, которые были аргументами и до вставок (на рисунке заштрихованы), игнорируя новые строки. Таким образом, пользователю в каждом подобном случае предстоит редактировать итоговую строку, что, конечно, очень обременительно. В нашем примере в А6 придется изменить выражение =СУММ(A3:A4) на =СУММ(A2:A5).

Что можно предпринять для учета этих обстоятельств? Нужно сделать так, чтобы крайние верхняя и нижняя строки перестали быть таковыми.

1. Неудобств, связанных с вставкой данных над областью суммирования, можно легко избежать, если включить в нее заголовок столбца, т.е. изменить формулу в A4 на =СУММ(A1:A3). Такой заголовок обязательно должен быть нечислового типа, иначе он будет участвовать в суммировании. Если же он содержит числа, можно поступить иначе – непосредственно под заголовком, но выше собственно области данных включить пустую строку, участвующую в суммирования. Если затем такую строку скрыть, то внесение новой строки вверху видимого диапазона суммирования повлечет правильную автоматическую коррекцию итоговой функции.

2. Для корректной вставки строки непосредственно над строкой, где формируется сумма, также целесообразно иметь пустую строку, включенную в область суммирования. В этом случае добавление новой строки над суммой (точнее над пустой строкой) не потребует какой-либо правки итоговой функции. Неудобством является сама пустая строка – она может попасть в готовый напечатанный документ.

Заметим, что включение дополнительных технических строк/столб­цов в обрабатываемую область может искажать результаты некоторых функций, что иногда делает невозможным применение указанных подходов. Если несколько усложнить выражение итоговой строки, можно избежать проблем со вставкой новых строк в любом месте диапазона суммирования. К этому вопросу мы вернемся позже.

G

Начиная с версии Excel-2000 предусмотрен механизм, во многом снимающий эту проблему. Соответствующая установка имеется в меню Сервис+Параметры+ вкладка Правка. Если здесь установить флажок Расширять форматы и формулы в списках, то дополнение таблицы в любом ее месте влечет автоматическое копирование в новую строку/столбец и форматов и формул из предыдущих строк/столбцов. Чтобы это свойство проявилось нужно только, чтобы повторяющиеся форматы и формулы были, по крайней мере, в трех из пяти предшествующих рядах таблицы. При этом не только автоматически добавляются формулы в заполняемой строке, но и корректируются итоговые выражения, находящиеся под/справа таблицы. Однако следует отметить, что указанный механизм может не работать в случаях сложных итоговых функций и в самом начале таблицы.

Арифметические функции

ü ABS(число) – абсолютное значение числа. Примеры: ABS(7)=ABS(-7)=7.

ü ЗНАК(число) – функция определения знака числа. Если аргумент больше 0, возвращается 1, если ноль – то 0, если отрицательное – то -1. Примеры: ЗНАК(3)=1, ЗНАК(0)=0, ЗНАК(-12)=-1.

ü ОСТАТ(делимое; делитель) – возвращает целочисленный остаток от деления двух чисел. Если делимое меньше делителя, результат равен делимому. Если деление выполняется без остатка, результат – ноль. Примеры: ОСТАТ(9;4)=1, ОСТАТ(5;11)=5, ОСТАТ(8;2)=0.

Функция определяет кратность одной величины другой. Так, например, при продаже товара общая сумма покупки должна быть кратна цене единицы товара, т.е. следует контролировать факт того, что

ОСТАТ(сумма;цена)=0.

ü ПРОИЗВЕД(число1; число2;...) – возвращает произведение чисел в списке аргументов. Если во множестве аргументов встречаются нечисловые элементы, они в произведении не участвуют (пропускаются).

Примеры: ПРОИЗВЕД(3;2;4)=3*2*4=24,

ПРОИЗВЕД(A1;2;A2:C3)=A1*2*A2*B2*C2*A3*B3*C3.

Функции округления

ü ОКРУГЛ(число; десятичные знаки) – функция округляет число до указанного количества десятичных знаков. Второй аргумент может быть положительным (округление происходит справа от десятичной запятой) и отрицательным (округление слева от запятой – младшие целые разряды округляются до нулей).

Примеры: ОКРУГЛ(512,4567;2)=512,46, ОКРУГЛ(512,4567;-2)=500.

Замечание. Функции округления и операции форматирования данных внешне могут привести к одному и тому же результату. Следует иметь в виду, что форматирование изменяет только вид числа, а округление фактически меняет это число (в примере число 512,4567 действительно превратилось в 500). Очевидно, что Excel обрабатывает не те значения, которые видит пользователь на экране, а те, что хранятся в его памяти, которые могут и отличаться между собой.

ü ОКРВНИЗ(число; множитель) – округляет заданное число до ближайшего меньшего числа, кратного второму аргументу. Примеры: ОКРВНИЗ(23,4;0,5)=23, ОКРВНИЗ(5;3)=3, ОКРВНИЗ(5;1,5)=4,5.

ü ОКРВВЕРХ(число; множитель) – округляет заданное чис­ло до ближайшего большего числа, кратного второму аргументу. Примеры: ОКРВВЕРХ(23,4;5)=25, ОКРВВЕРХ(5;3)=6, ОКРВВЕРХ(5;1,5)=6.

Функции ОКРВВЕРХ() и ОКРВНИЗ() удобны, например, в случае, когда нужно отпустить товар, расфасованным в тару фиксированной емкости (например, 150 единиц в ящике). Положим, поступил заказ на 1000 единиц. Тогда возможны два варианта (с избытком или с недостатком)

ОКРВВЕРХ(1000;150)=1050штук или ОКРВНИЗ(1000;150)=900штук,

которые и предлагаются покупателю.

ü ОКРУГЛВНИЗ(число; количество цифр) – округляет число по модулю до ближайшего меньшего целого с заданным количеством цифр. Если параметр количество цифр равен 0 или опущен, число ок­ругляется до ближайшего меньшего целого. Если количество цифр >0, число округляется до заданного количества десятичных разрядов после запятой. Если <0, целая часть числа округляется слева от десятичной запятой на заданное количество цифр.

Примеры: ОКРУГЛВНИЗ(82599,8;-2)=82500, ОКРУГЛВНИЗ(567,8176;2)=567,81, ОКРУГЛВНИЗ(567,987;)=567.

ü ОКРУГЛВВЕРХ(число; количество цифр) – округляет число по модулю до ближайшего большего целого с заданным количеством цифр.

Примеры: ОКРУГЛВВЕРХ(82501,8;-2)=82600, ОКРУГЛВВЕРХ(567,8123;2)=567,82, ОКРУГЛВВЕРХ(567,001;)=568.

ü ЦЕЛОЕ(число) – функция возвращает ближайшее снизу целое число от аргумента.

Примеры: ЦЕЛОЕ(5,8)=5, ЦЕЛОЕ(-5,8)=-6.

ü ОТБР(число) – отбрасывает все дробные разряды числа. Примеры: ОТБР(5,8)=5, ОТБР(-5,8)=-5.

ü ЧЁТН(число) – округляет число до ближайшего четного.

Примеры: ЧЁТН(23,4)=24, ЧЁТН(2)=2, ЧЁТН(-3)=-4, ЧЁТН(3)=4.

ü НЕЧЁТ(число) – округляет число до ближайшего нечетного.

Примеры: НЕЧЁТ(23,4)=25, НЕЧЁТ(3)=3, НЕЧЁТ(4)=5, НЕЧЁТ(-4)=-5.

Обе функции ЧЁТН() и НЕЧЁТ() положительные значения округляют вверх, а отрицательные – вниз (напомним, что ноль число четное).

Степенные функции

ü КОРЕНЬ(число) – извлекает квадратный корень из положительного числа. Пример: КОРЕНЬ(4)=2.

ü СТЕПЕНЬ(число; степень) – возводит число в заданную степень. Пример: СТЕПЕНЬ(3;2)=32=9.

ü EXP(степень) – возвращает результат возведения в степень основания натурального логарифма (е =2,71878...). Пример: EXP(2)= е 2=7,389.

ü LOG10(число) – вычисляет десятичный логарифм числа.

ü LOG(число; основание) – вычисляет логарифм числа по заданному основанию.

ü LN(число) – вычисляет натуральный логарифм числа. Функция является обратной по отношению к функции EXP(). Примеры: LOG10(1000)=3, LOG(32;2)=5, LN(EXP(4))=4.