Читайте также:
|
Тема № 9. Функции Excel.
Константы в формулах массива. Обычно формула при обработке нескольких аргументов возвращает одно значение; в качестве аргумента формулы может при этом выступать либо ссылка на ячейку, содержащую значение, либо само значение. Для создания ссылки на диапазон ячеек используется формула массива, позволяющая ввести в одну ячейку массив значений. Этот массив значений называется массивом констант; удобен он тем, что при этом не требуется заполнять значениями вспомогательные ячейки. Чтобы создать массив констант, выполните следующие действия:
· весь массив заключается в фигурные скобки "{ }";
· значения столбцов разделяются запятыми ",";
· значения строк разделяются точками с запятой ";".
Например, вместо ввода четырех чисел (10, 20, 30, 40) в отдельные ячейки их можно ввести в массив, в одну ячейку в фигурных скобках: {10,20,30,40}. Такой массив констант является матрицей (в данном случае вектором) размерности 1 на 4 и соответствует ссылке на 1 строку и 4 столбца. Чтобы представить значения “10, 20, 30, 40” и “50, 60, 70, 80”, находящиеся в расположенных друг под другом ячейках, можно создать массив констант размерностью 2 на 4, причем строки будут отделены друг от друга точкой с запятой, а значения в столбцах — запятыми: {10,20,30,40;50,60,70,80}. Для получения дополнительных сведений по вводу значений, используемых в массиве констант, нажмите кнопку.
Элементы массива констант
· Массив констант может состоять из чисел, текста, логических значений (например ИСТИНА или ЛОЖЬ) или значений ошибок (например #Н/Д).
· Числа в массиве могут быть целыми, с десятичной точкой или экспоненциальными.
· Текст должен быть взят в двойные кавычки, например "Четверг".
· Массив констант может состоять из элементов разного типа, например {1,3,4;ИСТИНА,ЛОЖЬ,ИСТИНА}.
· Элементы массива должны быть константами, но не формулами.
· Массив констант не может содержать знаки доллара ($), круглых скобок и процента (%).
· Массив констант не может содержать ссылок.
· Массив констант не может иметь столбцы или строки разного размера.
Применение некоторых функций не очевидно и требует пояснений. Часть из них мы сегодня и рассмотрим.
Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ, возвращающей текущую дату по системных часов.
Вычисление времени и даты в Microsoft Excel. Microsoft Excel запоминает дату в виде числа, называемого значением, а время – в виде десятичной части этого значения (время является частью даты). Даты и значения времени представляются числами; чтобы их можно складывать и вычитать, а также использовать в других вычислениях. Например, чтобы определить число дней между двумя датами, можно вычесть одну дату из другой. При изменении формата ячеек, содержащих компоненты даты и времени, на основной формат можно отобразить дату или время в виде числа с десятичной точкой. Microsoft Excel для Windows и Microsoft Excel для Macintosh используют по умолчанию различные системы дат.
Microsoft Excel поддерживает обе системы дат: система дат 1900 и 1904. По умолчанию, Microsoft Excel для Windows использует систему дат 1900. Чтобы воспользоваться системой дат 1904, выберите команду Параметры в меню Сервис, а затем — вкладку Вычисления. Установите флажок Система дат 1904.
В следующей таблице представлены первая и последняя даты для каждой системы, а также соответствующие им значения.
| Система дат | Первая дата | Последняя дата |
| 1 января 1900 г. (значение 1) | 31 декабря 9999 г. (значение 2958525) | |
| 2 января 1904 г. (значение 1) | 31 декабря 9999 г. (значение 2957063) |
Чтобы изменить систему дат, установите или снимите флажок система дат 1904 на вкладке Вычисления в пункте Параметры (меню Сервис).
При открытии документа, подготовленного в другой аппаратной платформе, смена системы дат происходит автоматически. Например, при открытии в Microsoft Excel для Windows документа, созданного в Microsoft Excel для Macintosh, параметр система дат 1904 будет выбран автоматически.
В числовом формате даты цифры справа от десятичной запятой представляют время; цифры слева от десятичной запятой представляют дату. Например, в системе дат 1900, дата в числовом формате 367,5 представляет код даты и времени: соответствующий 12 часам дня 1 января 1901 года.
Если при вводе даты указаны только две последние цифры года, Microsoft Excel добавит первые две по следующим правилам:
· если число лежит в интервале от 00 до 29, то оно интерпретируется как год с 2000 по 2029. Например, если в ячейку введена дата 28.5.19, Microsoft Excel распознает ее как: 28 мая 2019 г.
· если число лежит в интервале от 30 до 99, то оно интерпретируется как год с 1930 по 1999. Например, если в ячейку введена дата 28.5.91, Microsoft Excel распознает ее как: 28 мая 1991 г.
¨ ТДАТА – Возвращает текущую дату и время в числовом формате.
Синтаксис: ТДАТА()
Функция ТДАТА меняет свое значение только при расчете листа Excel или при выполнении макроса, содержащего эту функцию. Значение этой функции не обновляется непрерывно.
Примеры
Если используется система дат 1900 и встроенные часы компьютера установлены на 12:30:00 1 января 1987 года, то:
ТДАТА() равняется 31778,52083
Десять минут спустя ТДАТА() равняется 31778,52778
¨ ДАТАВозвращает дату в числовом формате для заданной даты. Для получения более подробной информации о числовом формате даты см. ТДАТА.
Синтаксис: ДАТА(год; месяц; день)
Год - это число от 1900 до 2078.
Месяц - это число, представляющее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Например, ДАТА(90;14;2) возвращает числовой формат даты 2 февраля 1991 года.
День - это число, представляющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число прибавляется к первому дню указанного месяца. Например, ДАТА(91;1;35) возвращает числовой формат даты 4 февраля 1991 года.
· Функция ДАТА наиболее полезна в формулах, в которых год, месяц и день являются формулами, а не константами.
Примеры
При использовании системы дат 1900 (по умолчанию в Microsoft Excel для Windows), ДАТА(91; 1; 1) равняется 33239, то есть числовому формату даты 1 января 1991 года.
При использовании системы дат 1904 (по умолчанию в Microsoft Excel для Macintosh), ДАТА(91; 1; 1) равняется 31777, то есть числовому формату даты 1 января 1991 года.
¨ ДАТАЗНАЧ – Возвращает числовой формат даты, представленной в виде текста, т.е. она преобразует дату из текстового представления в числовой формат.
Синтаксис: ДАТАЗНАЧ(дата_как_текст)
Дата_как_текст – это текст, содержащий дату в формате даты Microsoft Excel. При использовании в Microsoft Excel для Windows системы дат по умолчанию, дата_как_текст должна представлять собой дату в диапазоне от 1 января 1900 года до 31 декабря 2078 года. При использовании в Microsoft Excel для Macintosh системы дат по умолчанию, дата_как_текст должна представлять собой дату в диапазоне от 1 января 1904 года до 31 декабря 2078 года. Функция ДАТАЗНАЧ возвращает значение ошибки #ЗНАЧ!, если дата_как_текст выходит за этот диапазон.
Если в аргументе дата_как_текст опущен год, то ДАТАЗНАЧ использует текущий год из встроенных часов компьютера. Информация о времени в аргументе дата_как_текст игнорируется.
· Большинство функций автоматически преобразует даты в числовой формат.
Примеры
Следующие примеры используют систему дат 1900:
ДАТАЗНАЧ("22.8.55") равняется 20323
ДАТАЗНАЧ("22-АВГ-55") равняется 20323
Пусть встроенные часы компьютера установлены на 1993 год и используется Система дат 1900:
ДАТАЗНАЧ("5-ИЮЛ") равняется 34155
¨ ДЕНЬ – Возвращает номер дня в месяце для даты в числовом формате. День возвращается как целое число диапазоне от 1 до 31.
Синтаксис: ДЕНЬ(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Аргумент дата_в_числовом_формате может быть задан как текст, например "15-4-93" или "15-Апр-1993", а не как число. Текст автоматически преобразуется в дату в числовом формате.
Примеры
ДЕНЬ("4-Янв") равняется 4
ДЕНЬ("15-Апр-1993") равняется 15
ДЕНЬ("11.8.93") равняется 11
¨ ДНЕЙ360 – Возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Эта функция используется для расчета платежей, если Ваша бухгалтерия основана на двенадцати 30-дневных месяцах.
Синтаксис: ДНЕЙ360(нач_дата; кон_дата; метод)
Нач_дата и кон_дата - это две даты, для которых требуется узнать количество дней между ними.
Метод - это логическое значение, которое определяет, какой метод, Европейский или Американский, должен использоваться при вычислениях
ЛОЖЬ или опущено Американский метод (NASD). Если начальная дата является 31-м числом месяца, то она полагается равной 30-ому числу того же месяца. Если конечная дата является 31-м числом месяца и начальная дата меньше, чем 30-ое число, то конечная дата полагается равной 1-ому числу следующего месяца, в противном случае конечная дата полагается равной 30-ому числу того же месяца.
ИСТИНА Европейский метод. Начальная и конечная даты, которые приходятся на 31-ое число месяца, полагаются равными 30-ому числу того же месяца.
Чтобы определить количество дней между двумя датами в нормальном году, следует использовать обычное вычитание --. Например, "31.12.93"-"1.1.93" равняется 364.
Пример
ДНЕЙ360("30.1.93"; "1.2.93") равняется 1
Если ячейка D10 содержит дату 30.1.93, а ячейка D11 содержит дату 1.2.93, то:
ДНЕЙ360(D10, D11) равно 1
¨ ДАТАМЕС – Возвращает дату в числовом формате, представляющую дату, отстоящую на заданное количество месяцев вперед или назад от заданной даты (нач_дата). Функция ДАТАМЕС используется для вычисления срока погашения или даты платежа, приходящуюся на тот же день месяца, что и дата выпуска.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки, меню Сервис.
Синтаксис: ДАТАМЕС(нач_дата;число_месяцев)
Нач_дата – это дата, соответствующая начальной дате.
Число_месяцев – это количество месяцев до или после даты нач_дата. Положительное значение аргумента число_месяцев означает будущие даты; отрицательное значение означает прошедшие даты.
· Если нач_дата не являются допустимой датой, то функция ДАТАМЕС возвращает значение ошибки #ЧИСЛО!.
· Если число_месяцев не целое число, то оно усекается.
Примеры
ДАТАМЕС(ДАТАЗНАЧ("15.01.91");1) равняется 33284 или 15.02.91
ДАТАМЕС(ДАТАЗНАЧ("31.03.91");-1) равняется 33297 или 28.02.91
¨ КОНМЕСЯЦА – Возвращает числовой формат последнего дня месяца, отстоящего на указанное количество месяцев от нач_дата. Функция КОНМЕСЯЦА используется для вычисления даты вступления в силу или даты платежа, которая приходится на конец месяца.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем включить его с помощью диспетчера надстроек.
Синтаксис: КОНМЕСЯЦА(нач_дата; число_месяцев)
Нач_дата - это начальная дата в числовом формате.
Число_месяцев - это число месяцев до или после нач_дата. Положительное значение аргумента число_месяцев означает будущую дату; отрицательное значение означает прошедшую дату.
· Если нач_дата не является допустимой датой в числовом формате, то КОНМЕСЯЦА возвращает значение ошибки #ЧИСЛО!.
· Если число_месяцев не целое, то производится усечение.
· Если нач_дата плюс число_месяцев дают недопустимую дату в числовом формате, то КОНМЕСЯЦА возвращает значение ошибки #ЧИСЛО!.
Примеры
КОНМЕСЯЦА(ДАТАЗНАЧ("01.01.93"); 1) равняется 34028 или 28.2.93
КОНМЕСЯЦА(ДАТАЗНАЧ("01.01.93"); -1) равняется 33969 или 31.12.92
¨ МЕСЯЦ – Возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).
Синтаксис: МЕСЯЦ(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например "15-4-1993" или "15-Апр-1993", а не как число. Текст автоматически преобразуется в дату в числовом формате.
Примеры
МЕСЯЦ("6-Май") равняется 5
МЕСЯЦ(366) равняется 12
МЕСЯЦ(367) равняется 1
¨ ЧИСТРАБДНИ – Возвращает количество рабочих дней между нач_дата и кон_дата. Рабочими днями считаются дни за исключением выходных и праздничных дней. Функция ЧИСТРАБДНИ используется для вычисления оплаты работника на основе количества дней, отработанных в указанный период.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.
Синтаксис: ЧИСТРАБДНИ(нач_дата;кон_дата;праздники)
Нач_дата - это дата, которая представляет начальную дату.
Кон_дата - это дата, которая представляет конечную дату.
Праздники - это необязательный аргумент, являющийся множеством из одной или нескольких дат в числовом формате, которые должны быть исключены из числа рабочих дней, например, государственные праздники.
· Если любой из аргументов не является допустимой датой, то функция ЧИСТРАБДНИ возвращает значение ошибки #ЧИСЛО!.
Пример
ЧИСТРАБДНИ(ДАТАЗНАЧ("1.10.91"); ДАТАЗНАЧ("1.12.91");
ДАТАЗНАЧ("28.11.91")) равняется 43
¨ СЕГОДНЯ – Возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени.
Синтаксис: СЕГОДНЯ()
¨ ДЕНЬНЕД – Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (Воскресенье) до 7 (Суббота).
Синтаксис: ДЕНЬНЕД(дата_в_числовом_формате;тип)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Аргумент дата_в_числовом_формате можно задать как текст, например, как "15 Апр 1993" или "4.15.93", а не как число. Текст автоматически преобразуется в дату в числовом формате.
Тип - это число, которое определяет тип возвращаемого значения.
Тип Возвращаемое число
1 или опущен Число от 1 (Воскресенье) до 7 (Суббота). Аналогично предыдущей версии Microsoft Excel.
2 Число от 1 (Понедельник) до 7 (Воскресенье)
3 Число от 0 (Понедельник) до 6 (Воскресенье)
Можно использовать функцию ТЕКСТ, чтобы преобразовать значение в нужный числовой формат при использовании системы дат 1900:
ТЕКСТ("4.16.90"; "ДДДД") равняется Понедельник
Примеры
ДЕНЬНЕД("14.2.90") равняется 4 (Среда)
Если используется система дат 1900:
ДЕНЬНЕД(29747,007) равняется 4 (Среда)
Если используется система дат 1904:
ДЕНЬНЕД(29747,007) равняется 3 (Вторник)
¨ РАБДЕНЬ – Возвращает число, представляющее дату, отстоящую на заданное количество рабочих дней вперед или назад от даты нач_дата. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ используется, чтобы исключить выходные дни или праздники при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней. Чтобы отобразить число в виде даты, выполните команду Ячейки... меню Формат, выберите Дата в списке Числовые форматы, а затем выберите формат даты в списке Тип.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.
Синтаксис: РАБДЕНЬ(нач_дата;количество_дней;праздники)
Нач_дата – это начальная дата.
Количество_дней – это количество не выходных и не праздничных дней до или после нач_дата. Положительное значение аргумента количество_дней означает будущую дату; отрицательное значение - означает прошедшую дату.
Праздники - это необязательный список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например государственные праздники. Список может представлять собой группу ячеек или массив чисел, являющихся датами.
· Если нач_дата не является допустимой датой в числовом формате, то функция РАБДЕНЬ возвращает значение ошибки #ЧИСЛО!.
· Если нач_дата плюс количество_дней не является допустимой датой, то функция РАБДЕНЬ возвращает значение ошибки #ЧИСЛО!.
· Если количество_дней не целое, то оно усекается.
Примеры
РАБДЕНЬ(ДАТАЗНАЧ("03.01.91"); 5) равняется 33248 или 10.01.91
Если 7 и 8 января 1991 года являются праздниками, то:
РАБДЕНЬ(ДАТАЗНАЧ("03.01.91"); 5; {33245; 33246}) равняется 33252 или 14.01.91
¨ ГОД – Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999.
Синтаксис: ГОД(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например, как "19 Мар 1955" или "19.3.55", а не как число. Текст автоматически преобразуется в дату в числовом формате.
Примеры
ГОД("5.7.90") равняется 1990
Если используется система дат 1900 (по умолчанию в Microsoft Excel для Windows), то:
ГОД(0,007) равняется 1900
ГОД(29747,007) равняется 1981
(по умолчанию в Microsoft Excel для Macintosh), то:
ГОД(0,007) равняется 1904
ГОД(29747,007) равняется 1985
¨ ДОЛЯГОДА – Возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной). Функция ДОЛЯГОДА служит для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.
Синтаксис: ДОЛЯГОДА(нач_дата;кон_дата;базис)
Нач_дата – это дата, которая соответствует начальной дате.
Кон_дата – это дата, которая соответствует конечной дате.
Базис – это тип используемого способа вычисления дня.
Базис Способ вычисления дня
0 или опущен US (NASD) 30/360
1 Фактический/фактический
2 Фактический/360
3 Фактический/365
4 Европейский 30/360
· Все аргументы усекаются до целых.
· Если нач_дата или кон_дата не являются допустимой датой, то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.
· Если базис < 0 или если базис > 4, то функция то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.
Примеры
ДОЛЯГОДА("01.01.93";"30.06.93";0) равняется 0,5
ДОЛЯГОДА("01.01.93";"01.07.93";3) равняется 0,49863
¨ ЧАС – Возвращает час, соответствующий заданной дате в числовом формате. Час определяется как целое в интервале от 0 (12:00 AM) до 23 (11:00 PM).
Синтаксис: ЧАС(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например "16:48:00" или "4:48:00 PM", а не как число. Текст автоматически преобразуется в дату в числовом формате. Для получения более подробной информации о числовом формате даты см. ТДАТА.
Примечание Microsoft Excel для Windows и Microsoft Excel для Macintosh используют по умолчанию различные системы дат.
Примеры
ЧАС(0,7) равняется 16
ЧАС(29747,7) равняется 16
ЧАС("3:30:30 PM") равняется 15
¨ МИНУТЫ – Возвращает минуты, соответствующие аргументу дата_в_числовом_формате. Минуты определяются как целое в интервале от 0 до 59.
Синтаксис: МИНУТЫ(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например "16:48:00" или "4:48:00 PM", а не как число. Текст автоматически преобразуется в числовой формат даты.
Примеры МИНУТЫ("4:48:00 PM") равняется 48
МИНУТЫ(0,01) равняется 14
МИНУТЫ(4,02) равняется 28
¨ СЕКУНДЫ – Возвращает секунды, соответствующее аргументу дата_в_числовом_формате. Секунды определяются как целое в интервале от 0 (нуля) до 59. Функция СЕКУНДЫ используется, чтобы получить секунды момента времени, заданного датой в числовом формате.
Синтаксис: СЕКУНДЫ(дата_в_числовом_формате)
Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Аргумент дата_в_числовом_формате можно задать как текст, например, как "16:48:23" или "4:48:47 PM", а не как число. Текст автоматически преобразуется в дату в числовом формате.
Примеры
СЕКУНДЫ("4:48:18 PM") равняется 18
СЕКУНДЫ(0,01) равняется 24
СЕКУНДЫ(4,02) равняется 48
¨ ВРЕМЯ – Возвращает дату в числовом формате для заданного времени. Дата в числовом формате, возвращаемая функцией ВРЕМЯ - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).
Синтаксис: ВРЕМЯ(часы;минуты;секунды)
Часы - это число от 0 (нуля) до 23, представляющее час.
Минуты - это число от 0 до 59, представляющее минуту.
Секунды - это число от 0 до 59, представляющее секунду.
Примеры
ВРЕМЯ(12; 0; 0) равняется 0,5, что эквивалентно 12:00:00
ВРЕМЯ(16; 48; 10) равняется 0,700115741, что эквивалентно 16:48:10
ТЕКСТ(ВРЕМЯ(23; 18; 14); "ЧЧ:ММ:СС") равняется "23:18:14"
¨ ВРЕМЗНАЧ – Возвращает числовой формат для времени, представленного аргументом время_как_текст. Время в числовом формате - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера). Функция ВРЕМЗНАЧ используется для преобразования времени, представленного в виде текста, в числовой формат времени.
Синтаксис: ВРЕМЗНАЧ(время_как_текст)
Время_как_текст - это текстовая строка, содержащая время в любом формате, допустимом в Microsoft Excel. Информация о дате в аргументе время_как_текст игнорируется.
Примеры
ВРЕМЗНАЧ("2:24") равняется 0,1
ВРЕМЗНАЧ("22 Авг 55 6:35") равняется 0,274305556
Функции обработки текста
С помощью функций обработки текста можно с помощью формул производить действия над строкам текста — например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. В примере ниже показано, как с помощью функций СЕГОДНЯ и ТЕКСТ создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг".
="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")
Список функций обработки текста:
СИМВОЛ
ПЕЧСИМВ
КОДСИМВ
СЦЕПИТЬ
РУБЛЬ
СОВПАД
НАЙТИ
ФИКСИРОВАННЫЙ
ЛЕВСИМВ
ДЛСТР
СТРОЧН
ПСТР
ПРОПНАЧ
ЗАМЕНИТЬ
ПОВТОР
ПРАВСИМВ
ПОИСК
ПОДСТАВИТЬ
Т
ТЕКСТ
СЖПРОБЕЛЫ
ПРОПИСН
ЗНАЧЕН
СИМВОЛ – Возвращает символ с заданным кодом. Функция СИМВОЛ используется, чтобы преобразовать числовые коды символов, которые получены из файлов с других компьютеров, в символы данного компьютера.
Операционная среда Таблица символов
Macintosh Таблица символов Macintosh
Windows ANSI
Синтаксис – СИМВОЛ(число)
Число - это число от 1 до 255, указывающее нужный символ. Символы выбираются из таблицы символов Вашего компьютера.
Примеры
СИМВОЛ(65) равняется "A"
СИМВОЛ(33) равняется "!"
ПЕЧСИМВ – Удаляет все непечатаемые символы из текста. Функция ПЕЧСИМВ используется в том случае, когда текст, импортированный из другого приложения, содержит символы, которые не могут быть напечатаны операционной системой. Например, можно использовать функцию ПЕЧСИМВ, чтобы удалить низкоуровневые компьютерные коды, которые часто встречаются в начале или в конце файла данных и не могут быть напечатаны.
Синтаксис – ПЕЧСИМВ(текст)
Текст – это любая информация на рабочем листе, из которой удаляются непечатаемые символы.
Примеры
Поскольку СИМВОЛ(7) возвращает непечатаемый символ:
ПЕЧСИМВ(СИМВОЛ(7)&"текст"&СИМВОЛ(7)) равняется "текст"
КОДСИМВ – Возвращает числовой код первого символа в текстовой строке. Возвращаемый код соответствует таблице символов, используемой на данном компьютере.
Синтаксис – КОДСИМВ(текст)
Текст - это текст, в котором требуется узнать код первого символа.
Примеры
КОДСИМВ("A") равняется 65
КОДСИМВ("Alphabet") равняется 65
СЦЕПИТЬ – Объединяет несколько текстовых строк в одну.
Синтаксис – СЦЕПИТЬ (текст1;текст2;...)
Текст1, текст2,... - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.
Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор "&".
Примеры
СЦЕПИТЬ("Суммарное "; "Значение") равняется "Суммарное Значение". Это эквивалентно выражению "Суммарное"&" "&"Значение".
Пусть имеется рабочий лист, в котором собраны данные о видах рыб, обитающих в реке, и пусть ячейка C2 содержит "вида", ячейка C5 содержит " речная форель" и ячейка C8 содержит 32. Тогда
СЦЕПИТЬ("Численность популяции ";C2;" ";C5;" составляет ";C8;" на километр.") равняется "Численность популяции вида речная форель составляет 32 на километр."
РУБЛЬ – Преобразует число в текст, используя денежный формат с округлением до заданного числа десятичных знаков. Используется следующий формат: # ##0,00 р.;- # ##0,00 р.
Синтаксис – РУБЛЬ(число; число_знаков)
Число - это либо число, либо ссылка на ячейку, содержащую число, либо формула, вычисление которой дает число.
Число_знаков - это число цифр справа от десятичной запятой. Если число_знаков отрицательно, то число округляется слева от десятичной запятой. Если число_знаков опущено, то оно полагается равным 2.
Наибольшее различие между форматированием ячейки, содержащей число, с помощью команды Ячейка в меню Формат и форматированием числа непосредственно с помощью функции РУБЛЬ состоит в том, что функция РУБЛЬ преобразует свой результат в текст. Число, которое форматируется с помощью команды Ячейка по-прежнему остается числом. Однако, можно продолжать использовать числа, отформатированные функцией РУБЛЬ в формулах, потому что Microsoft Excel преобразует числа, введенные как текст, в числовые значения в процессе вычислений.
Примеры
РУБЛЬ(1234,567; 2) равняется "1234,57 р."
РУБЛЬ(1234,567; -2) равняется "1200 р."
РУБЛЬ(-1234,567; -2) равняется "-1200 р."
РУБЛЬ(-0,123; 4) равняется "-0,1230 р."
РУБЛЬ(99,888) равняется "99,89 р."
СОВПАД – Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают и ЛОЖЬ в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для того, чтобы проверить, входит ли некоторый текст в документ.
Синтаксис – СОВПАД(текст1; текст2)
Текст1 - это первая текстовая строка.
Текст2 - это вторая текстовая строка.
Примеры
СОВПАД("слово"; "слово") равняется ИСТИНА
СОВПАД("Слово"; "слово") равняется ЛОЖЬ
СОВПАД("с лово"; "слово") равняется ЛОЖЬ
Чтобы проверить, соответствует ли введенное пользователем значение одному из значений, заданных интервалом, следует ввести следующую формулу в ячейку как массив. Чтобы ввести формулу как массив в отдельную ячейку, нажмите клавиши CTRL+SHIFT+ENTER в Microsoft Excel для Windows или +ENTER в Microsoft Excel для Macintoch. Имя ПроверяемоеЗначение ссылается на ячейку, содержащую введенное пользователем значение; имя ИнтервалДляСравнений ссылается на список текстовых значений, с которыми производится сравнение.
{=ИЛИ(СОВПАД(ПроверяемоеЗначение; ИнтервалДляСравнений))}
НАЙТИ – Находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и начальную положение начала искомого текста относительно крайнего левого символа просматриваемого текста. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК, функция НАЙТИ учитывает регистр и не допускает символов шаблона.
Синтаксис – НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
Искомый_текст - это искомый текст.
· Если искомый_текст - это "" (пустая строка), то функция НАЙТИ считает подходящим первый символ в просматриваемой строке (то есть возвратит значение аргумента нач_позиция или 1).
· Искомый_текст не должен содержать никаких символов шаблона.
Просматриваемый_текст - это текст, содержащий искомый текст.
Нач_позиция - это позиция символа, с которой следует начинать поиск. Первый символ в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.
· Если искомый_текст не входит в просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.
· Если нач_позиция меньше или равна нулю, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.
· Если нач_позиция больше длины строки просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.
Примеры
НАЙТИ("М";"Мадам Смирнова") равняется 1
НАЙТИ("м";"Мадам Смирнова") равняется 5
НАЙТИ("м";"Мадам Смирнова";6) равняется 8
Предположим, что на рабочем листе хранится список деталей с их серийными номерами, и нужно выделить список наименований деталей без серийных номеров. Можно использовать функцию НАЙТИ для поиска символа #, а затем функцию ПСТР, чтобы исключить серийный номер. Пусть ячейки A2:A4 содержат следующий список деталей с серийными номерами: "Керамические изоляторы #124-6745-87", "Медные катушки #12-671-6772", "Переменные сопротивления #116010".
ПСТР(A2;1;НАЙТИ(" #";A2;1)-1) возвращает "Керамические изоляторы"
ПСТР(A3;1;НАЙТИ(" #";A3;1)-1) возвращает "Медные катушки"
ПСТР(A4;1;НАЙТИ(" #";A4;1)-1) возвращает "Переменные сопротивления"
ФИКСИРОВАННЫЙ – Округляет число до заданного количества десятичных цифр, форматирует число в десятичном формате с использованием запятых и точек и возвращает результат в виде текста.
Синтаксис – ФИКСИРОВАННЫЙ(число; число_знаков; без_разделителей)
Число - это число, которое округляется и преобразуется в текст.
Число_знаков - это число цифр справа от десятичной запятой.
Без_разделителей - это логическое значение, причем если аргумент без_разделителей имеет значение ИСТИНА, то ФИКСИРОВАННЫЙ не включает разделители тысяч в возвращаемый текст. Если аргумент без_разделителей имеет значение ЛОЖЬ или опущен, то возвращаемый текст будет включать разделители как обычно.
Числа в Microsoft Excel не могут иметь более 15 значащих цифр, но число_знаков может быть задано вплоть до 127.
Если число_знаков отрицательно, то число округляется слева от десятичной запятой.
Если число_знаков опущено, то оно полагается равным 2.
Наибольшее различие между форматированием ячейки, содержащей число, с помощью команды Ячейка в меню Формат и форматированием числа непосредственно с помощью функции ФИКСИРОВАННЫЙ состоит в том, что функция ФИКСИРОВАННЫЙ преобразует свой результат в текст. Число, которое форматируется с помощью команды Ячейка, по-прежнему остается числом.
Примеры
ФИКСИРОВАННЫЙ(1234,567; 1) равняется "1234,6"
ФИКСИРОВАННЫЙ(1234,567; -1) равняется "1230"
ФИКСИРОВАННЫЙ(-1234,567; -1) равняется "-1230"
ФИКСИРОВАННЫЙ(44,332) равняется "44,33"
ПСТР – Возвращает заданное число символов из строки текста, начиная с указанной позиции.
Синтаксис – ПСТР(текст; начальная_позиция;количество_символов)
Текст - это текстовая строка, содержащая извлекаемые символы.
Начальная_позиция - это позиция первого символа, извлекаемого из текста. Первый символ в тексте имеет начальную позицию 1 и так далее.
Если начальная_позиция больше, чем длина текста, то функция ПСТР возвращает строку "" (пустой текст).
Если начальная_позиция меньше, чем длина текста, но начальная_позиция плюс количество_символов превышают длину текста, то функция ПСТР возвращает символы вплоть до конца текста.
Если начальная_позиция меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
Количество_символов указывает, сколько символов нужно вернуть. Если количество_символов отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
Примеры
ПСТР("Поток жидкости"; 1; 5) равняется "Поток"
ПСТР("Поток жидкости"; 7; 20) равняется "жидкости"
ПСТР("1234"; 5; 5) равняется "" (пустой текст)
См. также примеры к функциям КОДСИМВ и НАЙТИ.
Т – Возвращает текст, ссылка на который задается аргументом значение.
Синтаксис – Т(значение)
Значение - это проверяемое значение. Если значение является текстом или ссылается на текст, то функция Т возвращает само это значение. Если значение не является текстом и не ссылается на текст, то функция Т возвращает "" (пустой текст).
В общем случае нет необходимости использовать функцию Т в формулах, поскольку Microsoft Excel в случае необходимости автоматически преобразует значения. Эта функция предназначена для совместимости с другими системами электронных таблиц.
Примеры
Если ячейка B1 содержит текст "Снегопад", то:
Т(B1) равняется "Снегопад"
Если ячейка B2 содержит число 19, то:
Т(B2) равняется ""
Т("Истина") равняется "Истина"
Т(ИСТИНА) равняется ""
Текст – Преобразует значение в текст в заданном числовом формате.
Синтаксис: ТЕКСТ(значение; формат)
Значение - это либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение.
Формат - это числовой формат в текстовой форме с вкладки Число диалога Формат ячеек. Формат не может содержать звездочку (*) и не может быть Общим числовым форматом.
Форматирование ячейки с помощью вкладки Число (команда Ячейки... меню Формат) меняет только формат, но не значение. Использование функции ТЕКСТ преобразует значение в форматированный текст, и результат больше не участвует в вычислениях как число.
Примеры
ТЕКСТ(2,715; "0,00 р.") равняется "2,72 р."
ТЕКСТ("15.4.91"; "Д МММ, ГГГГ") равняется "15 Апр, 1991"
ЗНАЧЕН – Преобразует строку текста, отображающую число, в число.
Синтаксис – ЗНАЧЕН(текст)
Текст - это текст в кавычках или ссылка на ячейку, содержащую текст, который нужно преобразовать. Текст может быть в любом формате, допускаемом Microsoft Excel для числа, даты и времени. Если текст не удовлетворяет ни одному из этих форматов, то функция ЗНАЧЕН возвращает значение ошибки #ЗНАЧ!.
Обычно нет необходимости использовать функцию ЗНАЧЕН в формулах, поскольку Microsoft Excel автоматически преобразует текст в число при необходимости. Эта функция предназначена для совместимости с другими программами электронных таблиц.
Примеры
ЗНАЧЕН("1000 р.") равняется 1000
ЗНАЧЕН("16:48:00")-ЗНАЧЕН("12:00:00") равняется "16:48:00"-"12:00:00" равняется 0,2, то есть времени в числовом формате, эквивалентному 4 часам 48 минутам.
ЛИНЕЙН – Расчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Для получения дополнительных сведений о формулах массива нажмите кнопку.
Уравнение для прямой линии имеет следующий вид:
y = mx + b или y = m1x1 + m2x2 +... + b (в случае нескольких интервалов значений x)
где зависимое значение y является функцией независимого значения x. Значения m - это коэффициенты, соответствующие каждой независимой переменной x, а b - это постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.
Синтаксис: ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)
ЭФФЕКТ – Возвращает фактическую годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов, составляющих год.
Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.
Синтаксис:
ЭФФЕКТ(номинальная_ставка;периодов_в_году)
Номинальная_ставка - это номинальная годовая процентная ставка.
Периодов_в_году - это количество периодов, составляющих год.
Финансовые функции – С помощью финансовых функций осуществляются такие типичные финансовые расчеты, как вычисление суммы платежа по ссуде, объем периодической выплаты по вложению или ссуде, стоимость вложения или ссуды по завершении всех отложенных платежей.
Аргументами финансовых функций часто являются следующие величины:
- будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей;
- количество выплат – общее количество платежей или периодов выплат;
- выплата – объем периодической выплаты по вложению или ссуде;
- текущее значение – начальная стоимость вложения или ссуды. Так, начальная стоимость ссуды равна, собственно, сумме займа;
- ставка – процентная ставка или скидка по вложению или ссуде;
- режим выплат – режим выплат, с которым осуществляются выплаты (в конце или в начале
Функции ссылки и автоподстановки – Если необходимо осуществлять поиск в списках или таблицах или если необходимо найти ссылку к ячейке, воспользуйтесь функциями ссылки и автоподстановки. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.
Информационные функции предназначены для определения типа данных, хранимых в ячейке. Информационные функции проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ. Так, если ячейка содержит четное значение, функция ЕЧЁТН возвращает значение ИСТИНА. Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией СЧИТАТЬПУСТОТЫ, либо ЕПУСТО.
Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое — если оно ложно.
§ И
§ ЛОЖЬ
§ ЕСЛИ
§ НЕ
§ ИЛИ
§ ИСТИНА
ЕСЛИ – Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется для условной проверки значений и формул.
Синтаксис: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.
Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.
· До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки, см. последний из приведенных ниже примеров.
· Функция ЕСЛИ всегда возвращает значение, возвращаемое вычисленным аргументом значение_если_истина и значение_если_ложь.
· Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива. Если какой-либо из аргументов значение_если_истина или значение_если_ложь является действием, то все действия выполняются.
Примеры
В следующем примере, если значение ячейки A10 - 100, то лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.
ЕСЛИ(A10=100;СУММ(B5:B15);"")
Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925.
Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений с помощью следующих формул:
ЕСЛИ(B2>C2;"Превышение бюджета";"OK") равняется "Превышение бюджета"
ЕСЛИ(B3>C3;"Превышение бюджета";"OK") равняется "OK"
Предположим, что нужно назначить буквенную категорию числам, на которые ссылаются по имени СреднийБалл. Категории приведены в следующей таблице.
| Средний Балл | Категория |
| Больше 89 | A |
| От 80 до 89 | B |
| От 70 до 79 | C |
| От 60 до 69 | D |
| Меньше 60 | F |
Тогда можно использовать вложенные функции ЕСЛИ:
ЕСЛИ(СреднийБалл>89;"A";ЕСЛИ(СреднийБалл>79;"B";ЕСЛИ(СреднийБалл>69;"C";ЕСЛИ(СреднийБалл>59;"D";"F"))))
В предыдущем примере второе предложение ЕСЛИ является в то же время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ является аргументом значение_если_ложь для второго предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет значение ИСТИНА, то возвращается значение "A". Если первое лог_выражение имеет значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ и так далее.
Функция "И". Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Синтаксис: И(логическое_значение1; логическое_значение2;...)
Логическое_значение1, логическое_значение2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
· Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения.
· Если аргумент, который является ссылкой или массивом, содержит тексты или пустые ячейки, то такие значения игнорируются.
· Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.
Примеры
И(ИСТИНА; ИСТИНА) равняется ИСТИНА
И(ИСТИНА; ЛОЖЬ) равняется ЛОЖЬ
И(2+2=4; 2+3=5) равняется ИСТИНА
Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то:
И(B1:B3) равняется ЛОЖЬ
Если ячейка B4 содержит число между 1 и 100, то:
И(1<B4; B4<100) равняется ИСТИНА
Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 и сообщение "Значение вне интервала" в противном случае. Тогда, если ячейка B4 содержит число 104, то выражение:
ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется "Значение вне интервала", а если ячейка B4 содержит число 50, то выражение:
ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется 50
ИЛИ – Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис: ИЛИ(логическое_значение1;логическое_значение2;...)
Логическое_значение1, логическое_значение2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
· Аргументы должны быть выражены логическими значениями, такими как ИСТИНА или ЛОЖЬ, массивами или ссылками, которые содержат логические значения.
· Если аргумент, который является массивом или ссылкой, содержит тексты, пустые значения или значения ошибок, то эти значения игнорируются.
· Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.
· Можно использовать функцию ИЛИ как формулу массива, чтобы проверить, имеются ли значения в массиве. Для того, чтобы ввести формулу массива, нажмите CTRL+SHIFT в Microsoft Excel 97 для Windows или +ENTER в Microsoft Excel 97 для Macintosh.
Примеры
ИЛИ(ИСТИНА) равняется ИСТИНА
ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ
Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то:
ИЛИ(A1:A3) равняется ИСТИНА
НЕ – Меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
Синтаксис: НЕ(логическое_значение)
Логическое_значение - это значение или выражение, которое при вычислении дает ИСТИНА или ЛОЖЬ. Если логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; Если логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.
Примеры
НЕ(ЛОЖЬ) равняется ИСТИНА
НЕ(1+1=2) равняется ЛОЖЬ
ИСТИНА – Возвращает логическое значение ИСТИНА.
Синтаксис: ИСТИНА()
Можно непосредственно ввести значение ИСТИНА в ячейки и формулы без использования этой функции. Функция ИСТИНА предназначена для совместимости с другими системами электронных таблиц.
ЛОЖЬ – Возвращает логическое значение ЛОЖЬ.
Синтаксис: ЛОЖЬ()
Можно непосредственно ввести слово ЛОЖЬ в рабочий лист или в формулу, и Microsoft Excel будет интерпретировать его как логическое значение ЛОЖЬ.
Тема дополнительная Макросы.
Использование макросов для автоматизации наиболее часто выполняемых задач
Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это серия команд и функций, хранящихся в модуле 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 Укажите нужные параметры на вкладке Рисунок.
Для получения справки по конкретному параметру нажмите кнопку с вопросительным знаком и выберите нужный параметр.
Чтобы использовать рисунок в качестве маркера данных на графике, точечной и незаполненной лепестковой диаграммах, выберите рисунок на рабочем листе, листе диаграммы или в программе редактирования рисунков, выберите команду Копировать (меню Правка), укажите ряд данных и выберите команду Вставить (меню Правка).
Дата добавления: 2015-11-13; просмотров: 120 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Возможности запроса | | | Лекция 12_04 (Excel) |