Лекция 10.

Электронные таблицы Excel. Основные термины и определения

 

1.  Термины и Определения

2.  Адресация относительная и абсолютная.

3.  Ввод информации.

4.  Запись формул.

5.  Некоторые встроенные функции

6.  Практические советы.

7.  Условное форматирование.

8.  Использование имен диапазонов.

 

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

В ЭТ информация организована в виде прямоугольной таблицы. Информационная структура в такой таблице не статистическая, а динамическая, т.е. с изменением исходных данных происходит автоматический пересчет вычисляемых данных. 

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

Рабочая книга - основной документ Excel, который состоит из отдельных рабочих листов, в которых могут храниться данные. Ячейка - пересечение строки и столбца. Каждая ячейка имеет свое обозначение (адрес ячейки).

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

Ссылка на ячейку (адрес ячейки) - обозначение столбца и номера строки, в которых расположена ячейка.

Каждая ячейка имеет оригинальный адрес: столбец/строка (например, А5: ячейка, находящаяся на пересечении столбца А и строки 5). 

Группа ячеек - прямоугольная часть таблицы.

Формула - это выражение, которое определяет способ вычисления ячейки.

Заголовок строки – серая область с номером строки в левой части экрана.

Заголовок столбца – серая область с буквой или номером столбца  в верхней части каждого столбца. 

2. Адресация относительная и абсолютная.

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

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

Различия между относительными и абсолютными ссылками

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

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

Ссылка, в которой одна из координат является относительной, а вторая абсолютной называются смешанными. Например, $B6, B$2.

Rm. Переключение между относительными и абсолютными ссылками  - F4. 

3. Ввод информации.

Excel разрешает вводить в ячейки следующие виды информации:

§  Числовые значения  (например, числа 15,25; $29.95, 33% и даже простые дроби, в этом случае между целой и дробной частью ставят пробел: 2 3/7).

§  Текстовые значения (например, слова «Итого», «1-й квартал» и т.д.

§  Даты и время суток.

§  Формулы.

§  Примечания, предназначенные для вас или других пользователей.

§  Гиперссылки на адреса Интернета и другие документы.

§  Картинки, фотографии, карты и иллюстрации.

Каждый тип информации имеет свои собственные характеристики формата. Это означает, что Excel выводит на экран элементы каждого типа по-разному.

4.  Запись формул.

Если значение ячейки определяется в результате вычислений, то в ячейку записывается формула. Формула начинается со знака "равно" (=). При записи формул используют:

 числа;

 знаки арифметических операций (в прядке выполнения: ^ * / + -);  скобки;  ссылки;  функции;  знаки сравнений (<,>,<=,>=,<>).

5.  Некоторые встроенные функции

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

В состав Excel входит более 250 функций, поделенные на отдельные категории: математические, статистические, логические, дата и время и др. Описание функций можно посмотреть через операцию вставки функций: Вставка \ Функции…

Математические функции

ОСТАТ(число;делитель) - возвращает остаток от деления числа на делитель. Результат имеет такой же знак, как и делитель.

Например, =ОСТАТ(3;2) Ответ: 1

ОКРУГЛ(число;число_разрядов) - округляет число до указанного количества десятичных разрядов по правилам математики.

Число— округляемое число. 

Число_разрядов - количество десятичных разрядов, до которого нужно округлить число. Если 0 – то до целого.

Например,

=ОКРУГЛ(3,2;0)   Ответ: 3                                             =ОКРУГЛ(3,8;0)   Ответ: 4   

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

Например,

=ОКРУГЛВВЕРХ(3,2;0)   Ответ: 4            =ОКРУГЛ(3,14;1)   Ответ: 3,2   ОКРУГЛВНИЗ(число;число_разрядов) - число всегда округляется с недостатком.

Например,

=ОКРУГЛВНИЗ (3,8;0)   Ответ: 3                                  =ОКРУГЛ(3,88;1)   Ответ: 3,8  

СЛЧИС() - возвращает случайное число из диапазона [0,1). Новое случайное число возвращается при каждом вычислении рабочего листа.   Примечание.
Диапазон	Тип	Формула	Пример
[a,b)	Вещ.	СЛЧИС()*(b-a)+a	=СЛЧИС()*(10-5)+5 – случ.число из [5,10)
[a,b]	целый	ОКРУГЛ(СЛЧИС()*(b-a);0)+a	=ОКРУГЛ(СЛЧИС()*(10-5);0)+5 – одно из чисел 1,2,3,4 или 5
СУММ(аргумент1; аргумент2; …) – суммирует указанные числа.  В качестве аргументов можно указывать данные различных типов, но в подсчете участвуют только числа. Как правило, аргументами являются диапазоны ячеек. СУММЕСЛИ(Диапазон; критерий; Сумм_диапазон) – суммирует значения ячеек внутри диапазона «Сумм_диапазон», для которых соответствующие значения из «Диапазон» удовлетворяют заданному критерию.  Диапазон - это диапазон ячеек, среди  которого ищем те, значения которых удовлетворяют критерию.  Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен как 32, "32", ">32", "яблоки". Сумм_диапазон – диапазон фактических ячеек для суммирования. Ячейки в «Сумм_диапазон» суммируются, только если соответствующие им ячейки в аргументе «Диапазон» удовлетворяют критерию. Если «Сумм_диапазон» опущен, то суммируются ячейки из «Диапазона».

Например,

	A	B	C
1	Стоимость дома,  у.е.	Комиссионные, у.е.
2	10000	700
3	20000	1400
4	30000	2100
5	40000	2800
	=СУММЕСЛИ(A2:A5;">21000";B2:B5)

В ячейку с формулой (А6) будет занесено число 4900 (В4+В5=2100+2800)

СУММПРОИЗВ(массив1;массив2;массив3; ...)- перемножает соответствующие элементы заданных массивов (диапазонов) и возвращает сумму произведений.

Массив1, массив2, массив3, ... — от 2 до 30 массивов (диапазонов), чьи компоненты нужно перемножить, а затем сложить; они должны иметь одинаковые размерности.. 

Примечание. СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые. 

Например,

	A	B	C
1	Наименование	К-во кг	Цена за кг
2	Яблоки	1,2	45
3	Груши	2	48
4	Апельсины	5,3	60
5	Мандарины	4	65
6	Итого:	=СУММПРОИЗВЕД($B$2: $B$5; $C$2:$C$5)

Статистические функции

МИН(аргумент1;аргумент2; ...) -  возвращает наименьшее значение среди перечисленных аргументов.

Число1, число2, ... - от 1 до 30 чисел или диапазонов, среди которых требуется найти наименьшее. 

МАКС(аргумент1;аргумент2; ...) -  возвращает наибольшее значение среди перечисленных аргументов.

НАИБОЛЬШИЙ(массив;k) - возвращает k-ое наибольшее значение из множества данных . 

где Массив - это массив или диапазон данных, для которых определяется k-ое наибольшее значение.

K - это позиция (начиная с наибольшей) в массиве или диапазоне ячеек данных.

Эта функция используется, чтобы выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать, чтобы определить наилучший, второй или третий результат в баллах, показанный при тестировании.

Например,

	А	B	C	D	E	F
1	Баллы	4	5	5	3	4
2		=НАИБОЛЬШИЙ ($B$1:$F$1;1)
3		=НАИБОЛЬШИЙ ($B$1:$F$1;2)
4		=НАИБОЛЬШИЙ ($B$1:$F$1;3)

Значения, которые будут занесены в ячейки:

В2	В3	В4
НАИБОЛЬШИЙ ($B$1:$F$1;1)	НАИБОЛЬШИЙ ($B$1:$F$1;2)	НАИБОЛЬШИЙ ($B$1:$F$1;3)
5	5	4
	Обратите внимание, что второе наибольшее значение в блоке не 4, как можно было бы подумать, а 5, т.е. совпадает с первым наибольшим значением

НАИМЕНЬШИЙ(массив;k) - возвращает k-ое наименьшее значение в множестве данных. 

СРЗНАЧ(аргумент1; аргумент2; …) – находит среднее арифметическое аргументов. 

В качестве аргументов можно указывать данные различных типов, но в подсчете участвуют только числа. Как правило, аргументами являются диапазоны ячеек. Пустые ячейки не учитываются, ячейки содержащие  нулевые значения, учитываются.

СЧЁТ(аргумент1; аргумент2; …) – подсчитывает количество чисел в списке аргументов. 

В качестве аргументов можно указывать данные различных типов, но в подсчете участвуют только числа, а также данные в формате «дата и время». Как правило, аргументами являются диапазоны ячеек.

СЧЁТЕСЛИ(диапазон; критерий) - подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. 

Диапазон - это диапазон, в котором нужно подсчитать ячейки. 

Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". 

Например,

	A	B	C
1	Наименование	Сорт	Количество
2	Яблоки	1	10
3	Апельсины	1	14
4	Персики	1	21
5	Яблоки	2	12
6	=СЧЁТЕСЛИ(A2:A5;"Яблоки ")	=СЧЁТЕСЛИ(В2:В5;"=1")	=СЧЁТЕСЛИ(В2:В5;">15")
	В А6 будет занесено число 2 (т.к. значение "Яблоки" содержится в двух ячейках диапазона (А2 и А5)).	В B6 будет занесено число 3 (т.к. значение "1" содержится в трех ячейках диапазона (В2-В4)).	В C6 будет занесено число 1 (т.к. только в одной ячейке диапазона (С4) содержится значение больше 15).

Логические функции

Логическое выражение (значение)  - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)  где Значение_если_истина - это значение, которое возвращается в ячейку, если лог_выражение имеет значение ИСТИНА.

Значение_если_ложь - это значение, которое возвращается в ячейку, если лог_выражение имеет значение ЛОЖЬ.

В качестве Значение_если_истина или Значение_если_ложь может быть новая функция ЕСЛИ. =если(а1="д"; "правильно";"нет")

И(логическое_значение1; логическое_значение2; ...) - возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Например, чтобы определить, принадлежит число из ячейки В4 диапазону [1,100], т.е. 1<=В4<=100, надо записать: 

=(И(B4>=1; B4<=100)

ИЛИ(логическое_значение1;логическое_значение2; ...) - возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА и ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Например, ИЛИ (B4<0; B4>10) равняется ИСТИНА, если ячейка B4 содержит число,  находящееся вне диапазона [0,10] и ЛОЖЬ – в противном случае. НЕ(логическое_значение) - меняет на противоположное логическое значение своего аргумента.  Если логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА и наоборот, еЕсли логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.

Пример. Определить, сколько дней в году из ячейки А1.

Решение.

Проблема сводится к определению, является ли год високосным. Невисокосными являются года, которые не делятся на 4 и года, которые делятся на 100, но не делятся на 400.

=ЕСЛИ(ИЛИ(ОСТАТ(A1;4)<>0;И(ОСТАТ(A1;100 )=0;ОСТАТ(A1;400 )<>0));365;366)

Функции для работы с датой и временем ВРЕМЯ(часы;минуты;секунды) - возвращает целое число, представляющее определенное время. где  Часы  — число от 0 до 32767, задающее часы. Если значение больше 23, его можно разделить на 24; остаток от деления будет соответствовать значению часов. 

Например, ВРЕМЯ(27;0;0) = ВРЕМЯ(3;0;0) = 0,125 = 3:00 AM

Минуты  — число от 0 до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. 

Секунды  — число от 0 до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды.  

ДАТА(год;месяц;день) - возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был Общий, результат будет отформатирован как дата.

Где Год - аргумент, который может иметь от одной до четырех цифр. 

Месяц - число, представляющее месяц года. 

Если значение аргумента больше 12, введенное число месяцев отсчитывается от первого месяца указанного года. Например, ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года.

День - число, представляющее день месяца. 

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

Например, ДАТА(2008;1;32) возвращает число, соответствующее 1 февраля 2008 года. Пример. Случайным образом сгенерировать список дней рождений.

Решение.

 

Формула1: =ОКРУГЛ(СЛЧИС()*(B$3-B$2);0)+B$2 Формула2: =ДАТА(B5;C5;D5) Примечание. Чтобы полученную с помощью формулы дату скопировать как числовое значение, надо выделить диапазон; выбрать команду Правка \ Копировать;  выбрать команду Правка \ Специальная вставка; поставить переключатель Вставить значения. ДЕНЬНЕД(дата;тип) - возвращает день недели, соответствующий дате. День недели определяется как целое число. Тип - это число, которое определяет способ нумерации дней недели.
Тип	Возвращаемое число
1 или опущен	Число от 1 (воскресенье) до 7 (суббота).
2	Число от 1 (понедельник) до 7 (воскресенье)
3	Число от 0 (понедельник) до 6 (воскресенье)
СЕГОДНЯ() – возвращает текущую дату. Пример. Определить примерный возраст человека по дате рождения, находящиеся в ячейке В2.

Решение.

 

Текстовые функции

СЦЕПИТЬ (значение1;значение2;...) – объединяет несколько текстовых строк в одну. где значение1, значение2, ... - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

Примечание. Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор «&».

Например, Имеется таблица, в которой Фамилия, Имя и Отчество располагаются в трех столбцах А,В и С соответственно. Чтобы объединить ФИО в один столбец надо: =СЦЕПИТЬ(A1;" ";B1;" ";C1)

Финансовые  функции

Аргументы.  Ставка - процентная ставка по ссуде. Кпер – количество выплат по ссуде. Пс – значение ссуды или общая ((полная, приведенная)) стоимость. Бс  - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т.е. для займа, например, значение Бс равно 0. Плт - это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Если аргумент опущен, должно быть указано значение аргумента Пс. Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Тип	Когда нужно платить
0 или опущен	В конце периода
1	В начале периода
ПЛТ(ставка;кпер;пс;бс;тип) - возвращает размер периодического платежа, необходимого для погашения ссуды за определенный период времени.

Пример. Определить размер ежемесячных платежей при ссуде в $10000 на срок в 3 года под 9% годовых.

=ПЛТ(9%/12;3*12;10000)

Примечание. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, надо умножить возвращаемое функцией ПЛТ значение на «кпер».

БС(ставка;кпер;плт;пс;тип) - возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

Пример. Сколько будет на счету, если в течение 30 лет  вкладывать по 2000 в месяц при 10% годовых.

Решение.

=БС(10%/12;30*12;-2000;;1)

(расчет процентов в начале месяца, если 0 – в конце и тогда общий результат меньше)

СТАВКА(кпер;плт;пс;бс;тип;предположение) - возвращает норму прибыли за один период. Пример. Подсчитать планируемую норму прибыли, если вы ссудили другу 120000 на строительство дома с ежегодной выплатой 32000 в течение 5 лет.

Решение. 

=СТАВКА(5;32000;-120000) Ответ=10%

ПС(ставка;кпер;плт;бс;тип) - возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат.

Пример. Условия страховки: 500 руб. платится в конце каждого месяца в течении 20 лет при 8% годовых.. Определить, общую стоимость выплат.

Решение.

=ПС(8%/12;20*12;500;;0)

Функции проверки свойств и значений

ЕПУСТО(ссылка) – логическая функция, принимает значение ИСТИНА, если указанная ячейка пуста и ЛОЖЬ, в противном случае.

ЕЧИСЛО (ссылка) – логическая функция, принимает значение ИСТИНА, если в указанной ячейке - число.

ЕТЕКСТ (ссылка) – логическая функция, принимает значение ИСТИНА, если в указанной ячейке - текст. (Если в ячейке число, пусто или дата, то возвращает - ложь).

6. Практические советы.

1.            Если вид выводимой информации не совпал с вашими ожиданиями (вместо даты – число или наоборот), то надо изменить формат ячейки: Формат \ Ячейки: Число.

2.            Редактировать содержимое формул можно двумя способами: либо нажав F2, либо щелкнув мышью в строке формул (при ее отсутствии на экране, вызвать ее можно через команду Вид \ Строка формул);

3.            При написании формул, содержащих ссылки на другие ячейки (например, =В1) или диапазон ячеек (например, =сумм($В3:$В5)) можно адреса выбирать с помощью щелчка мышью по нужной ячейке или выделив мышью нужный диапазон.

4.            Выбрать тип ссылки (относительная, абсолютная, смешанная) – F4.

5.            Если необходимо выделить одновременно ячейки из разных концов таблицы (например, ячейку А3, ячейки с В2 по В8 и ячейку С4), то выделяют их при нажатой клавише Ctrl .

6.            Если необходимо размножить содержимое одной ячейки в ближайшие ячейки по вертикали или горизонтали, то необходимо "потянуть" ячейку за квадратик, расположенный в правом нижнем углу.

7.            Аналогично можно, например, пронумеровать строки; для этого: в первых двух строках пишем номера 1 и 2, затем выделяем обе ячейки и тянем за правый нижний угол; этот же способ годиться и для букв, и для написания названий месяцев и т.п.

8.            Объединение нескольких ячеек в одну и возврат: выделить группу ячеек, которые необходимо объединить, включить режим объединения строк (Формат \ Ячейки \ Выравнивание:  v  Объединение ячеек) или с помощью пиктограммы .

9.            Расположить многострочный текст в одной ячейке можно, если включить соответствующий режим (Формат \ Ячейки \ Выравнивание:  v  Переносить по словам), разбить одну строку на две при этом можно нажав Alt+Enter.

10.        Обрамление и заполнение лучше всего делать в самом конце, потому что при копировании, перемещении ячеек оно нарушается. 

Ссылки и массивы

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр) - ищет значение в крайнем левом столбце указанной таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Буква «В» в имени функции ВПР означает «вертикальный».

Искомое_значение — это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.

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

Номер_столбца — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение.  

Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и т.д.

Интервальный_просмотр – значение логического типа, которое определяет, нужно ли, чтобы ВПР искала точное (при ЛОЖЬ) или приближенное (ИСТИНА или пропущено)соответствие.

Примечания. 

§  Значения в первом столбце аргумента «таблица» могут быть текстовыми строками, числами или логическими значениями. 

§  Текстовые строки сравниваются без учета регистра букв. 

§  Если Интервальный_просмотр ИСТИНА, то значения в первом столбце таблицы должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. 

§  Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение. 

§  Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента «таблица», то функция ВПР возвращает значение ошибки #Н/Д. 

§  Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д. 

Пример. 

Оклад работников бюджетной сферы определяется по Единой тарифной сетке (ЕТС) и зависит от разряда. На листе 1 расположена таблица расчета оклада в зависимости от разряда. На листе Сотрудники список сотрудников с указанием разряда. Каждому сотруднику подставить оклад. 

 

При задании условного форматирования листа, оформление ячеек (заливка и граница ячейки, цвет, способ форматирования и начертания символов) меняется в зависимости от их содержимого. 

Это позволяет привлечь внимание к самым важным данным (например, росту биржевого курса или резкому увеличению расходов) или на неправильно введенные данные.

                                                                                                                                                                                      

                                                                 Рис.1                                                                                 Рис.2

 

Примечания.

1.    Чтобы в качестве условия форматирования использовать значения выделенных ячеек, надо выбрать параметр Значение, операцию сравнения, а затем ввести заданное значение (пример 1)или формулу. Перед формулой нужно поставить знак равенства (=) (пример 2).

2.    Для использования формулы в качестве критерия форматирования надо выбрать параметр Формула, а затем ввести формулу, принимающую логическое значение ИСТИНА или ЛОЖЬ (см. пример

3).

3.    Можно задать до трех Условий, после задания 1-го условия выбрать кнопку А также.

4.    Если ни одно из заданных условий не принимает истинного значения, формат ячеек остается прежним.

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

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

Пример 1. На столбец, в ячейки которого пользователь будет вводить номер месяца, задать условное форматирование.

Решение.

Если значение не попадает в диапазон [1,12], то применить условное форматирование (рис.3). 

 

Рис.3

Пример 2. В диапазоне [В2:В10] записаны оклады сотрудников отдела. Выделить цветом те из них, которые меньше среднего оклада по отделу.

Решение.

Если значение не попадает в диапазон [1,12], то применить условное форматирование (рис.4). 

 

Рис.4

Пример 3. Задать условное форматирование на ячейки D2:D7, при котором при вводе в ячейки значений отличных от М и Ж ячейки закрашиваются в серый цвет. Решение.

1.    На ячейку D2 задать условное форматирование, используя логическую функцию И.      

В данном случае для Excel не имеет значения прописные или строчные буквы М и Ж, поэтому предусматривать это вариант не  надо.  

Так как предполагается копировать значение ячейки в диапазон, то использовалась смешанная адресация. 

2.    Перейти на вкладку Формат и выбрать команду Вид. Задать цвет заливки.

3.    «Растянуть» первую ячейку диапазона на оставшиеся ячейки.

 

Рис.3

8. Использование имен диапазонов.

Диапазонам ячеек листа и отдельным ячейкам можно присвоить имя, после чего оно может быть использовано в любой формуле книги.

Требования к именам:

§  должны начинаться с буквы;

§  не могут содержать пробелов, вместо пробела используется знак подчеркивания;

§  должно быть уникальным;

§  рекомендуется не более чем из 15 символов. Чтобы присвоить диапазону имя надо:

1.    Выделить диапазон.

2.    Выбрать команду Вставка \ Имя \ Присвоить или задать имя в поле Имя. Чтобы изменить диапазон надо:

1.    Выбрать команду Вставка \ Имя \ Присвоить.

2.    В появившемся окне выбрать имя диапазона, который надо изменить; в строке Формула – указать новый диапазон. 

3.    Выбрать ОК. Чтобы удалить имя диапазона надо:

1.    Выбрать команду Вставка \ Имя \ Присвоить.

2.    В появившемся окне выбрать имя диапазона, который надо удалить.

3.    Выбрать кнопку Удалить.