16375

Функции Excel

Лекция

Информатика, кибернетика и программирование

Функции Excel. Константы в формулах массива. Обычно формула при обработке нескольких аргументов возвращает одно значение; в качестве аргумента формулы может при этом выступать либо ссылка на ячейку содержащую значение либо само значение. Для создания ссылки на диапаз

Русский

2013-06-20

972.91 KB

4 чел.

Функции 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.

ДАТАЗНАЧ Преобразует дату из текстового формата в числовой.

ДЕНЬ Преобразует дату в числовом формате в день месяца.

ДНЕЙ360 Вычисляет количество дней между двумя датами на основе 360-дневного года.

ДАТАМЕС Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.

КОНМЕСЯЦА Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

ЧАС Преобразует дату в числовом формате в часы.

МИНУТЫ Преобразует дату в числовом формате в минуты.

МЕСЯЦ Преобразует дату в числовом формате в месяцы.

ЧИСТРАБДНИ Возвращает количество рабочих дней между двумя датами.

ТДАТА Возвращает текущую дату и время в числовом формате.

СЕКУНДЫ Преобразует дату в числовом формате в секунды.

ВРЕМЯ Возвращает заданное время в числовом формате.

ВРЕМЗНАЧ Преобразует время из текстового формата в числовой формат.

СЕГОДНЯ Возвращает числовой формат текущей даты.

ДЕНЬНЕД Преобразует дату в числовом формате в день недели.

НОМНЕДЕЛИ Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата.

РАБДЕНЬ Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

ГОД Преобразует дату в числовом формате в год.

ДОЛЯГОДА Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

Вычисление времени и даты в Microsoft Excel. Microsoft Excel запоминает дату в виде числа, называемого значением, а время –в виде десятичной части этого значения (время является частью даты). Даты и значения времени представляются числами; чтобы их можно складывать и вычитать, а также использовать в других вычислениях. Например, чтобы определить число дней между двумя датами, можно вычесть одну дату из другой. При изменении формата ячеек, содержащих компоненты даты и времени, на основной формат можно отобразить дату или время в виде числа с десятичной точкой. Microsoft Excel для Windows и Microsoft Excel для Macintosh используют по умолчанию различные системы дат.

Microsoft Excel поддерживает обе системы дат: система дат 1900 и 1904. По умолчанию, Microsoft Excel для Windows использует систему дат 1900. Чтобы воспользоваться системой дат 1904, выберите команду Параметры в меню Сервис, а затемвкладку Вычисления. Установите флажок Система дат 1904. 

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

Система дат

Первая дата

Последняя дата

1900

1 января 1900 г.
(
значение 1)

31 декабря 9999 г.
(
значение 2958525)

1904

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 (Воскресенье) до 7 (Суббота). Аналогично предыдущей версии Microsoft Excel.

 Число от  1 (Понедельник) до 7 (Воскресенье)

Число от 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

  •  ДОЛЯГОДАВозвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной). Функция ДОЛЯГОДА служит для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период.

Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.

Синтаксис: ДОЛЯГОДА(нач_дата;кон_дата;базис)

Нач_дата – это дата, которая соответствует начальной дате.

Кон_дата – это дата, которая соответствует конечной дате.

Базис – это тип используемого способа вычисления дня.

Базис Способ вычисления дня

или опущен US (NASD) 30/360

 Фактический/фактический

Фактический/360

 Фактический/365

 Европейский 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 будет интерпретировать его как логическое значение ЛОЖЬ.


Функции ссылки и автоподстановки –Если необходимо осуществлять поиск в списках или таблицах или если необходимо найти ссылку к ячейке, воспользуйтесь функциями ссылки и автоподстановки. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в спискефункцию ПОИСКПОЗ.

АДРЕС Возвращает ссылку на отдельную ячейку рабочего листа в виде текста.

ОБЛАСТИ Возвращает количество областей в ссылке.

ВЫБОР Выбирает значение из списка значений по индексу.

СТОЛБЕЦ Возвращает номер столбца, на который указывает ссылка.

ЧИСЛСТОЛБ Возвращает количество столбцов в массиве или ссылке.

ГПР Ищет значение в первой строке массива и возвращает значение из ячейки в найденном столбце и указанной строке.

ГИПЕРССЫЛКА Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете.

ИНДЕКС Использует индекс для выбора значения из ссылки или массива.

ДВССЫЛ Возвращает ссылку, заданную текстовым значением.

ПРОСМОТР Ищет значения в векторе или массиве.

ПОИСКПОЗ Ищет значения в ссылке или массиве.

СМЕЩ Возвращает смещение ссылки относительно заданной ссылки.

СТРОКА Возвращает номер строки, определяемой ссылкой.

ЧСТРОК Возвращает количество строк в ссылке.

ДРВ Извлекает дату в режиме реального времени для программ, поддерживающих автоматизацию COM (Программирование объектов. Стандартное средство для работы с объектами некоторого приложения из другого приложения или средства разработки. Программирование объектов (ранее называемое программированием OLE) является функцией модели COM (Component Object Model, модель компонентных объектов).).

ТРАНСП Возвращает транспонированный массив.

ВПР Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце.

АДРЕС

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

Синтаксис

АДРЕС(номер_строки;номер_столбца ;тип_ссылки;а1;имя_листа)

Номер_строкиэто номер строки, используемый в ссылке ячейки.

Номер_столбцаэто номер столбца, используемый в ссылке ячейки.

Тип_ссылкиэто задание типа возвращаемой ссылки.

Возвращаемый тип ссылки

1 или опущен  Абсолютный

  Абсолютная строка; относительный столбец

  Относительная строка; абсолютный столбец

  Относительный

А1логическое значение, которое определяет тип ссылок: А1 или R1C1. Если аргумент а1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, то функция АДРЕС возвращает ссылку типа R1C1.

Имя_листаэто текст, определяющий имя рабочего листа, который используется для формирования внешней ссылки. Если имя_листа опущено, то внешние листы не используются.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Инструкции 

  1.  Создайте пустую книгу или лист.
  2.  Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. 

Выделение примера в справке.

  1.  Нажмите сочетание клавиш CTRL+C
  2.  На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
  3.  Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

A

B

1

Формула

Описание (результат)

2

=АДРЕС(2;3)

Абсолютная ссылка ($C$2)

3

=АДРЕС(2;3;2)

Абсолютная строка; относительный столбец (C$2)

4

=АДРЕС(2;3;2;ЛОЖЬ)

Абсолютная строка; относительный столбец в стиле ссылки R1C1 (R2C[3])

5

=АДРЕС(2;3;1;ЛОЖЬ;"[Книга1]Лист1")

Абсолютная ссылка на другую книгу и рабочий лист ([Книга1]Лист1!R2C3)

6

=АДРЕС(2;3;1;ЛОЖЬ;"Лист Microsoft Excel")

Абсолютная ссылка на другой рабочий лист ('Лист Microsoft Excel'!R2C3)

ОБЛАСТИ

Возвращает количество областей в ссылке. Областьэто интервал смежных ячеек или отдельная ячейка.

Синтаксис

ОБЛАСТИ(ссылка )

Ссылкаэто ссылка на ячейку или интервал ячеек; может относиться к нескольким областям. Если нужно задать несколько ссылок как один аргумент, то следует использовать дополнительные пары скобок, чтобы Microsoft Excel не интерпретировал точку с запятой как разделитель аргументов. Ниже 

Пример

1

2

3

4

A

B

Формула

Описание (результат)

=ОБЛАСТИ(B2:D4)

Количество областей в диапазоне (1)

=ОБЛАСТИ((B2:D4;E5;F6:I9))

Количество областей в диапазоне (3)

=ОБЛАСТИ(B2:D4 B2)

Количество областей в диапазоне (1)

ЧИСЛСТОЛБ

Возвращает количество столбцов в массиве или ссылке.

Синтаксис

ЧИСЛСТОЛБ(массив)

Массивэто либо массив, либо формула, формула, вырабатывающая массив, либо ссылка на интервал ячеек, для которых определяется количество столбцов.

Пример

1

2

3

A

B

Формула

Описание (результат)

=ЧИСЛСТОЛБ(C1:E4)

Число столбцов в ссылке (3)

=ЧИСЛСТОЛБ({1;2;3:4;5;6})

Число столбцов в массиве констант (3)

ВЫБОР

Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР используется, чтобы выбрать одно значение из списка, в котором может быть до 29 значений. Например, если значения от значение1 до значение7это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента номер_индекса.

Синтаксис

ВЫБОР(номер_индекса ;значение1;значение2;…)

Номер_индексаэто номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 29, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 29.

  •  Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, то функция ВЫБОР возвращает значение2 и так далее.
  •  Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!.
  •  Если номер_индекса является дробным, то он усекается до меньшего целого.

Значение1, значение2,...это от 1 до 29 аргументов-значений, из которых ВЫБОР, используя номер_индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, именами, формулами, функциями или текстами.

  •  Если номер_индекса является массивом (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.), то каждое значение вычисляется при выполнении функции ВЫБОР.
  •  Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями. 

Например, формула:

=СУММ(ВЫБОР(2;A1:A10;B1:B10;C1:C10))

эквивалентна формуле:

=СУММ(B1:B10)

которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.

В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, используя интервал B1:B10, то есть результат функции ВЫБОР, в качестве своего аргумента.

Пример 1

A

B

Данные

Данные

Первый

Гвозди

Второй

Винты

Третий

Гайки

Последний

Болты

Формула

Описание (результат)

=ВЫБОР(2;A2;A3;A4;A5)

Значение из второго аргумента A3 (Второй)

=ВЫБОР(4;B2;B3;B4;B5)

Значение из четвертого аргумента B5 (Болты)

Пример 2

A

Данные

23

45

12

10

Формула

Описание (результат)

=СУММ(A2:ВЫБОР(2;A3;A4;A5))

Суммирует диапазон A2:A4 (80)

СТОЛБЕЦ 

Возвращает номер столбца по заданной ссылке.

Синтаксис

СТОЛБЕЦ(ссылка)

Ссылкаэто ячейка или интервал ячеек, для которых определяется номер столбца.

  •  Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ.
  •  Если ссылка является интервалом ячеек, и если функция СТОЛБЕЦ введена как горизонтальный массив (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.), то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива.
  •  Ссылка не может ссылаться на несколько областей.

Пример

1

2

3

A

B

Формула

Описание (результат)

=СТОЛБЕЦ()

Столбец в котором отображается формула (1)

=СТОЛБЕЦ(A10)

Столбец ссылки (1)

ГПР

Ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже. Если сравниваемые значения расположены в столбце слева от искомых данных, то следует использовать функцию ВПР.

Буква Г в ГПР означает «горизонтальный».

Синтаксис

ГПР(искомое_значение;таблица ;номер_строки;интервальный_просмотр)

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

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

  •  Значения в первой строке аргумента «таблица» могут быть текстом, числами или логическими значениями.
  •  Если «интервальный_просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны быть расположены в возрастающем порядке: ...-2, -1, 0, 1, 2,... , A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.
  •  Текстовые строки считаются эквивалентными независимо от регистра букв.
  •  Данные можно упорядочить по возрастанию, слева направо. Для этого выделите эти данные и выберите в меню Данные команду Сортировка. Нажмите кнопку Параметры и выберите столбцы диапазона. Нажмите кнопку ОК. В поле Сортировать по выберите строку в списке и установите переключатель по возрастанию.

Номер_строкиэто номер строки в массиве «таблица», из которой будет возвращено сопоставляемое значение. Если «номер_строки» равен 1, то возвращается значение из первой строки аргумента «таблица», если «номер_строки» равен 2, то возвращается значение из второй строки аргумента «таблица», и так далее. Если «номер_строки» меньше 1, то функция ГПР возвращает значение ошибки #ЗНАЧ!; если «номер_строки» больше, чем количество строк в аргументе «таблица», то функция ГПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотрэто логическое значение, которое определяет, нужно ли, чтобы функция ГПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ГПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Заметки

  •  Если ГПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
  •  Если «искомое_значение» меньше, чем наименьшее значение в первой строке аргумента «таблица», то функция ГПР возвращает значение ошибки #Н/Д.

Пример

A

B

C

Оси

Подшипники

Болты

4

5

6

Формула

Описание (результат)

=ГПР("Оси";A1:C4;2;ИСТИНА)

Поиск слова «Оси» в строке 1 и возвращение значения из строки 2, находящейся в том же столбце (4)

=ГПР("Подшипники";A1:C4;3;ЛОЖЬ)

Поиск слова «Подшипники» в строке 1 и возвращение значения из строки 3, находящейся в том же столбце (7)

=ГПР("П";A1:C4;3;ИСТИНА)

Поиск «П» в строке 1 и возвращение значения из строки 3, находящейся в том же столбце. Так как «П» не точное совпадение, ближайшее меньшее значение: «Оси». (5)

=ГПР("Болты";A1:C4;4)

Поиск слова «Болты» в строке 1 и возвращение значения из строки 4, находящейся в том же столбце (11)

=ГПР(3;{1;2;3:"a";"b";"c";"d";"e";"f"};2;ИСТИНА)

Поиск числа 3 в строке 1 массива констант и возвращение значения из строки 2, находящейся в том же столбце. (c)

ИНДЕКС (функция ИНДЕКС)

http://office.microsoft.com/assistance/&ver=11В этой статье описаны синтаксис формулы и использование функции ИНДЕКС в Microsoft Excel.

Описание

Возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две формы: ссылочную и форму массива. 

Возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца.

Если первый аргумент функции ИНДЕКС является константной массива, используйте форму массива.

Синтаксис

ИНДЕКС(массив, номер_строки, [номер_столбца])

Аргументы функции ИНДЕКС описаны ниже.

  •  Массивобязательный аргумент. Диапазон ячеек или константа массива.
  •  Если массив содержит только одну строку или один столбец, аргумент "номер_строки" или "номер_столбца" соответственно не является обязательным.
  •  Если массив занимает больше одной строки и одного столбца, а из аргументов "номер_строки" и "номер_столбца" задан только один, то функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента "массив".
  •  Номер_строкиобязательный аргумент. Номер строки в массиве, из которой требуется возвратить значение. Если аргумент "номер_строки" опущен, аргумент "номер_столбца" является обязательным.
  •  Номер_столбцанеобязательный аргумент. Номер столбца в массиве, из которого требуется возвратить значение. Если аргумент "номер_столбца" опущен, аргумент "номер_строки" является обязательным.

Замечания

  •  Если используются оба аргументаи "номер_строки", и "номер_столбца",то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
  •  Если указать в качестве аргумента "номер_строки" или "номер_столбца" значение 0, функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальныйдля столбца. Чтобы ввести формулу массива, нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

Примечание. В Excel Web App невозможно создать формулу массива.

  •  Аргументы "номер_строки" и "номер_столбца" должны указывать на ячейку внутри заданного массива, в противном случае функция ИНДЕКС возвратит значение ошибки #ССЫЛ!.

Примеры

Пример 1

1

2

3

4


5


6

A

B

Данные

Данные

Яблоки

Лимоны

Бананы

Груши

Формула

Описание (результат)

=ИНДЕКС(A2:B3;2;2)

Значение ячейки на пересечении второй строки и второго столбца в диапазоне (Груши)

=ИНДЕКС(A2:B3;2;1)

Значение ячейки на пересечении второй строки и первого столбца в диапазоне (Бананы)

Пример 2

1


2


3

A

B

Формула

Описание (результат)

=ИНДЕКС({1;2:3;4};0;2)

Значение ячейки на пересечении первой строки и второго столбца в константе массива (2)

Значение ячейки на пересечении второй строки и второго столбца в константе массива (4)

Примечание. Формулу в этом примере необходимо ввести в настольном приложении Excel как формулу массива. После копирования примера на пустой лист выделите диапазон A2:A3, начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затемсочетание клавиш CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, единственным полученным результатом будет значение 2.

Ссылочная форма

Описание

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

Синтаксис

ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])

Аргументы функции ИНДЕКС описаны ниже.

  •  Ссылкаобязательный аргумент. Ссылка на один или несколько диапазонов ячеек.
  •  Если в качестве аргумента "ссылка" используется несмежный диапазон, его необходимо заключить в скобки
  •  Если каждая область в ссылке содержит только одну строку или один столбец, аргумент "номер_строки" или "номер_столбца" соответственно является необязательным. Например, для ссылки на единственную строку следует использовать форму ИНДЕКС(ссылка;;номер_столбца).
  •  Номер_строкиобязательный аргумент. Номер строки в диапазоне, заданном аргументом "ссылка", из которого требуется возвратить ссылку.
  •  Номер_столбцанеобязательный аргумент. Номер столбца в диапазоне, заданном аргументом "ссылка", из которого требуется возвратить ссылку.
  •  Номер_областинеобязательный аргумент. Диапазон в аргументе "ссылка", из которого требуется возвратить пересечение строки и столбца, заданных аргументами "номер_строки" и "номер_столбца". Первая введенная или выделенная область имеет номер 1, втораяи т. д. Если аргумент "номер_области" опущен, в функции ИНДЕКС используется область 1.
  •  Например, если аргумент "ссылка" определяет ячейки (A1:B4;D1:E4;G1:H4), номер области 1 соответствует диапазону A1:B4, номер области 2диапазону D1:E4, а номер области 3диапазону G1:H4.

Замечания

  •  После того как с помощью аргументов "ссылка" и номер_области" выбран диапазон, с помощью аргументов "номер_строки" и "номер_столбца" выбирается конкретная ячейка: номер строки 1 соответствует первой строке диапазона, номер столбца 1его первому столбцу и т. д. Ссылка, возвращаемая функцией ИНДЕКС, указывает на пересечение строки "номер_строки" и столбца "номер_столбца".
  •  Если указать в качестве аргумента "номер_строки" или "номер_столбца" значение 0, функция ИНДЕКС возвратит ссылку на целый столбец или целую строку соответственно.
  •  "Номер_строки", "номер_столбца" и "номер_области" должны указывать на ячейку внутри аргумента "ссылка", в противном случае функция ИНДЕКС возвращает значение ошибки #ССЫЛ!. Если аргументы "номер_строки" и "номер_столбца" опущены, функция ИНДЕКС возвращает область в аргументе "ссылка", заданную аргументом "номер_области".
  •  Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА("ширина";ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА("ширина";B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

Примечание. Функция ЯЧЕЙКА недоступна в Excel Web App.

Пример

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

A

B

C

Фрукты

Цена

Количество

Яблоки

0,69

40

Бананы

0,34

38

Лимоны

0,55

15

Апельсины

0,25

25

Груши

0,59

40

Миндаль

2,80

10

Кешью

3,55

16

Арахис

1,25

20

Грецкие орехи

1,75

12

Формула

Описание (результат)

=ИНДЕКС(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.

Пример

1

2

3

4

5

A

B

Данные

Данные

B2

1,333

B3

45

Егор

10

5

62

Формула

Описание (результат)

=ДВССЫЛ($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 В меню Сервис выберите подменю Макрос и выберите команду Запись.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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-й и 10-й строками включительно :10

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

25


 

А также другие работы, которые могут Вас заинтересовать

84816. РАЗРАБОТКА И АДАПТАЦИЯ САЙТА «ИНТЕГРА» для ИП Нафиков 141.79 KB
  В наши дни можно редко встретить компанию, которая бы не обладала бы web-сайтом. Так как, интернет стал для большинства людей источником необходимой информации и средством коммуникаций, практически все организации используют его в качестве своего лица. Шагая в ногу со временем, любая организация...
84817. Восприятие цвета 185.5 KB
  В современном мире все сферы деятельности людей подвержены влиянию новых информационных технологий (НИТ), что приводит к многообразным преобразованиям в сфере социальных отношений, материального и духовного производства.
84818. Особенности учета уставного капитала и учета расчетов с учредителями 78.3 KB
  Организация грамотного учета уставного капитала в современных условиях является весьма актуальной проблемой для многих предприятий ввиду постоянно изменяющейся законодательной базы в этой области а также целого ряда особенностей учета уставного капитала существуют различные трудности и проблемы в бухгалтерском...
84819. Расчет числа и порядка укладки укороченных рельсов на внутренних нитях кривых 2.5 MB
  Железнодорожные пути делятся на главные, станционные и специального назначения. Главные пути — это пути, соединяющие станции или другие раздельные пункты. К станционным относятся: приёмо-отправочные, сортировочные, вытяжные, погрузочно-выгрузочные, ходовые, соедини-тельные и др.
84820. Рекомендации по совершенствованию реализации функций контроля и регулирования на «Хлебозаводе №6» 87.2 KB
  Цель курсовой работы: закрепить знания по менеджменту, а именно в части контроля и регулирования в управлении предприятием. Поэтому основные задачи: рассмотреть понятия, факторы, характеристики контроля; проанализировать контроль и регулирование на конкретном предприятии.
84821. Анализ затрат на производство продукции 162.34 KB
  В работе анализируется динамика объёма производства в натуральном и стоимостном выражении сезонные колебания факторный анализ анализ динамики производства анализ структуры и динамики затрат на производство продукции.
84822. Расходы предприятия. Пути сокращения расходов предприятия 4.23 MB
  Экономика предприятия есть обобщенный экономический результат его деятельности как за прошлый, так и за данный период времени, находящий свое выражение в имуществе, в денежных, материальных и интеллектуальных ресурсах на каждый данный рассматриваемый период времени.
84823. Оценка и планирование внешнеэкономической деятельности и ценообразование на импортную продукцию 1.33 MB
  С целью стабильности и высокой эффективности своей хозяйственной деятельности каждая фирма планируя приобрести необходимую продукцию за рубежом должна предварительно изучить всю публикуемую информацию о ценовой и технической характеристиках данной продукции степень насыщенности рынка...