Функции проверки типа

Пример

A	B	C
Фрукты	Цена	Количество
Яблоки	0,69
Бананы	0,34
Лимоны	0,55
Апельсины	0,25
Груши	0,59
Миндаль	2,80
Кешью	3,55
Арахис	1,25
Грецкие орехи	1,75
Формула	Описание (результат)
=ИНДЕКС(A2:C6;2;3)	Пересечение второй строки и третьего столбца в диапазоне A2:C6, т. е. содержимое ячейки C3 (38)
=ИНДЕКС((A1:C6;A8:C11);2;2;2)	Пересечение второй строки и второго столбца во второй области (A8:C11), т. е. содержимое ячейки B9 (3,55)
=СУММ(ИНДЕКС(A1:C11;0;3;1))	Сумма третьего столбца в первой области диапазона (A1:C11) является суммой диапазона C1:C6 (216)
=СУММ(B2:ИНДЕКС(A2:C6;5;2))	Сумма значений из диапазона, начинающегося с ячейки B2 и заканчивающегося пересечением пятой строки и второго столбца диапазона A2:A6, т. е. сумма значений из диапазона B2:B6 (2,42)

ДВССЫЛ

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

Синтаксис

ДВССЫЛ(ссылка_на_ячейку;a1)

Ссылка_на_ячейку — это ссылка на ячейку, которая содержит либо ссылку в стиле А1, либо ссылку в стиле R1C1, либо имя, определенное как ссылка, либо ссылку на ячейку в виде текстовой строки. Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ возвращает значение ошибки #ССЫЛ!.

· Если ссылка_на_ячейку является ссылкой на другую рабочую книгу (внешней ссылкой), другая рабочая книга должна быть открытой. Если это не так, функция ДВССЫЛ возвратит значение ошибки #ССЫЛКА!.

A1 — это логическое значение, указывающее, какого типа ссылка содержится в ячейке ссылка_на_текст.

· Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.

· Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Пример

A	B
Данные	Данные
B2	1,333
B3
Егор
Формула	Описание (результат)
=ДВССЫЛ($A$2)	Значение ссылки в ячейке A2 (1,333)
=ДВССЫЛ($A$3)	Значение ссылки в ячейке A3 (45)
=ДВССЫЛ($A$4)	Если ячейке B4 было присвоено имя «Егор», будет возвращено значение из этой ячейки (10)
=ДВССЫЛ("B"&$A$5)	Значение ссылки в ячейке A5 (62)

При создании формулы, содержащей ссылку на ячейку, ссылка на ячейку будет обновлена, если (1) ячейка перемещалась с помощью команды Вырезать, использовавшейся для удаления ячейки или (2) перемещение ячейки связано с вставкой или удалением строк или столбцов. Если требуется, чтобы формула ссылалась на одну и ту же ячейку, независимо от удаления расположенных над ячейкой строк или перемещения ячеек, используйте функцию рабочего листа ДВССЫЛ. Например, для подобной ссылки на ячейку A10 используйте следующий синтаксис:

=ДВССЫЛ("A10")

Информационные функции предназначены для определения типа данных, хранимых в ячейке. Информационные функции проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ. Так, если ячейка содержит четное значение, функция ЕЧЁТН возвращает значение ИСТИНА. Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией СЧИТАТЬПУСТОТЫ, либо ЕПУСТО.

Тема дополнительная Макросы.

Использование макросов для автоматизации наиболее часто выполняемых задач

Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это серия команд и функций, хранящихся в модуле Visual Basic. Их можно выполнять всякий раз, когда необходимо выполнить данную задачу.

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

Редактор Visual Basic позволяет изменять макросы, а также копировать их либо из одного модуля в другой, либо между различными книгами. Кроме того, можно переименовывать модули, в которых хранятся макросы, или переименовывать сами макросы.

Запись макроса

1 В меню Сервис выберите подменю Макрос и выберите команду Запись.

2 Введите имя для макроса в соответствующее поле.

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

3 Чтобы выполнить макрос с клавиатуры с помощью сочетания клавиш, введите соответствующую букву в поле Сочетание клавиш. Для строчных букв используется сочетание CTRL+ буква, а для заглавных — CTRL+SHIFT+ буква, где буква — любая клавиша на клавиатуре. Буква, используемая в сочетании клавиш, не может быть цифрой или специальным символом. Заданное сочетание клавиш будет заменять любое установленное по умолчанию в Microsoft Excel, пока книга, содержащая данный макрос, открыта.

4 В поле Сохранить в книге выберите книгу, в которой должен быть сохранен макрос.

Чтобы макрос был доступен независимо от того, используется ли в данный момент Microsoft Excel, его следует сохранить в личной книге в папке XLStart.

Чтобы создать краткое описание макроса, введите необходимый текст в поле Описание.

5 Нажмите кнопку OK.

По умолчанию, при записи макроса используются абсолютные ссылки. Макрос, записанный с абсолютными ссылками, при выполнении всегда обрабатывает те же ячейки, которые обрабатывались при его записи. Для того, чтобы с помощью макроса обрабатывать произвольные ячейки, следует записать его с относительными ссылками. Для этого нажмите кнопку Относительная ссылка на панели инструментов Остановка записи. Относительные ссылки будут использоваться до конца текущего сеанса работы в Microsoft Excel или до повторного нажатия кнопки Относительная ссылка.

6 Выполните макрокоманды, которые нужно записать.

7 Нажмите кнопку Остановить запись на соответствующей панели инструментов.

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

Выполнение макроса в Microsoft Excel

1 Откройте книгу, которая содержит макрос.

2 В меню Сервис установите указатель на пункт Макрос и выберите команду Макросы.

3 В поле Имя макроса введите имя того макроса, который нужно выполнить.

4 Нажмите кнопку Выполнить.

Для прерывания выполнения макроса нажмите кнопку ESC.

Изменение макроса

Для изменения записанного макроса необходимо знакомство с редактором Visual Basic, который используется для написания и изменения макросов Microsoft Excel.

1 В меню Сервис установите указатель на пункт Макрос и выберите команду Макросы.

2 Введите имя макроса в соответствующее поле.

3 Нажмите кнопку Изменить.

Ссылки на ячейку или на группу ячеек

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

По умолчанию в Microsoft Excel используются ссылки A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536). Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона. В следующей таблице представлены варианты возможных ссылок.

Чтобы сослаться на Введите

Ячейку столбца A и 10-й строки A10

Диапазон ячеек столбца A с 10-й строки по 20-ю A10:A20

Диапазон ячеек в 15-й строке со столбца B по столбец E B15:E15

Все ячейки в 5-й строке 5:5

Все ячейки между 5-й и 10-й строками включительно 5:10

Все ячейки в столбце H H:H

Все ячейки между столбцами H и J включительно H:J

Можно воспользоваться стилем, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый R1C1, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы “R” указывается номер строки ячейки, после буквы “C” — номер столбца. Для получения дополнительных сведений о ссылках R1C1 нажмите кнопку.

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

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

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

Выполнение макроса из модуля Visual Basic

1 Откройте книгу, которая содержит макрос.

2 В меню Сервис установите указатель на пункт Макрос и выберите команду Макросы.

3 В поле Имя макроса введите имя того макроса, который нужно выполнить.

4 Нажмите кнопку Изменить.

5 Нажмите кнопку Выполнить макрос.

Совет. Чтобы выполнить другой макрос, находясь в редакторе Visual Basic, выберите команду Макросы в меню Сервис.

В поле Имя макроса введите имя того макроса, который нужно выполнить, а затем нажмите кнопку Выполнить.

Выполнение макроса

После того как макрос записан, его можно выполнить в Microsoft Excel или из редактора Visual Basic. Обычно макросы выполняются в Microsoft Excel; однако в ходе изменения их можно выполнить из редактора Visual Basic. Для прерывания выполнения макроса следует нажать клавишу ESC.

Предполагаемые действия

§ Выполнение макроса в Microsoft Excel

§ Выполнение макроса из модуля Visual Basic

Добавление рисунка к элементу диаграммы

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

1 Выберите элемент диаграммы, к которому нужно добавить рисунок.

2 Нажмите на стрелку рядом с кнопкой Цвет заливки, выберите цвет заливки, а затем — вкладку Рисунок.

3 Укажите нужный рисунок.

4 В поле Папка выберите диск, папку или адрес Интернета, где находится нужный рисунок, а затем дважды щелкните его кнопкой мыши.

5 Укажите нужные параметры на вкладке Рисунок.

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

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

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

Каждая из этих функций проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ в зависимости от типа. Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ.

Синтаксис

ЕПУСТО(значение)
ЕОШ(значение)
ЕОШИБКА(значение)
ЕЛОГИЧ(значение)
ЕНД(значение)
ЕНЕТЕКСТ(значение)
ЕЧИСЛО(значение)
ЕССЫЛКА(значение)
ЕТЕКСТ(значение)

Значение — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов.

Функция	Возвращает ИСТИНА, если
ЕЛОГИЧ	Значение ссылается на логическое значение.
ЕНД	Значение ссылается на значение ошибки #Н/Д (значение недоступно).
ЕНЕТЕКСТ	Значение ссылается на любой элемент, который не является текстом. (Заметим, что функция возвращает ИСТИНА, если значение ссылается на пустую ячейку).
ЕОШ	Значение ссылается на любое значение ошибки, кроме #Н/Д.
ЕОШИБКА	Значение ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!).
ЕПУСТО	Значение ссылается на пустую ячейку.
ЕССЫЛКА	Значение ссылается на ссылку.
ЕТЕКСТ	Значение ссылается на текст.
ЕЧИСЛО	Значение ссылается на число.

Заметки

· Аргумент этих функций не преобразуется. Для большинства других функций, например, если в качестве аргумента требуется число, то текстовое значение «19» преобразуется в число 19. Однако в формуле ЕЧИСЛО("19") «19» не преобразуется из текста в число, и функция ЕЧИСЛО возвращает значение ЛОЖЬ.

· Эти функции полезны в формулах и макросах для проверки результатов вычислений. Комбинируя эти функции с функцией ЕСЛИ, можно локализовать ошибки в формулах (см. следующие примеры).

Пример 1

A	B
Формула	Описание (результат)
=ЕЛОГИЧ(ИСТИНА)	Проверяет, является ли значение ИСТИНА логическим значением (ИСТИНА)
=ЕЛОГИЧ("ИСТИНА")	Проверяет, является ли значение «ИСТИНА» логическим значением (ЛОЖЬ)
=ЕЧИСЛО(4)	Проверяет, является ли значение 4 числом (ИСТИНА)

Пример 2

A
Данные
Золото
Регион1
#ССЫЛ!
330,92
#Н/Д
Формула	Описание (результат)
=ЕПУСТО(A2)	Проверяет, является ли ячейка C2 пустой (ЛОЖЬ)
=ЕОШИБКА(A4)	Проверяет, является ли #ССЫЛ! сообщением об ошибке (ИСТИНА)
=ЕНД(A4)	Проверяет, является ли #ССЫЛ! сообщением об ошибке #Н/Д (ЛОЖЬ)
=ЕНД(A6)	Проверяет является ли #Н/Д сообщением об ошибке #Н/Д (ИСТИНА)
=ЕОШ(A6)	Проверяет, является ли #Н/Д сообщением об ошибке (ЛОЖЬ)
=ЕЧИСЛО(A5)	Проверяет, является ли значение 330,92 числом (ИСТИНА)
=ЕТЕКСТ(A3)	Проверяет, является ли значение «Регион1» текстом (ИСТИНА)

ЕНЕЧЁТ Возвращает логическое значение ИСТИНА, если аргумент — нечетное число.

ЕТЕКСТ Возвращает логическое значение ИСТИНА, если аргумент ссылается на текст.

Ч Возвращает значение, преобразованное в число.

НД Возвращает значение ошибки #Н/Д.

ТИП Возвращает тип значения.

Дата добавления: 2014-01-07