Текстовые функции. Функция ВПР

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

► ПРОПИСН(текст)

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

Текст — текст, преобразуемый в верхний регистр. Текст может быть ссылкой на текст или текстовой строкой.

► СТРОЧН(текст)

Преобразует знаки в текстовой строке из верхнего регистра в нижний.

Текст — текст, преобразуемый в нижний регистр. Функция СТРОЧН не меняет знаков, которые не являются буквами.

► ПРОПНАЧ(текст)

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

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

► ЛЕВСИМВ (текст; количество_знаков)

возвращает указанное число знаков с начала текстовой строки.

Текст — текстовая строка, содержащая извлекаемые знаки.

Количество_знаков — количество знаков, извлекаемых функцией ЛЕВСИМВ.

· Количество_знаков должно быть больше либо равно нулю.

· Если количество_знаков больше длины текста, то функция ЛЕВСИМВ возвращает весь текст.

· Если количество_знаков опущено, то предполагается, что оно равно 1.

► ПРАВСИМВ (текст; количество_знаков)

возвращает указанное число последних знаков текстовой строки.

Текст — текстовая строка, содержащая извлекаемые знаки.

Количество_знаков — количество знаков, извлекаемых функцией ПРАВСИМВ.

· Количество_знаков должно быть больше либо равно нулю.

· Если количество_знаков больше длины текста, то функция ПРАВСИМВ возвращает весь текст.

· Если количество_знаков опущено, то предполагается, что оно равно 1.

► СЦЕПИТЬ (текст1;текст2;...)

Объединяет несколько текстовых строк в одну.

Текст1, текст2, ... — это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

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

► ПСТР(текст; начальная_позиция; число_знаков)

возвращает указанное число знаков из текстовой строки, начиная с указанной позиции.

Текст — текстовая строка, содержащая извлекаемые знаки.

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

Число_знаков указывает, сколько знаков требуется вернуть.

• Если начальная_позиция больше, чем длина текста, то функция ПСТР возвращает строку "" (пустой текст).
• Если начальная_позиция меньше, чем длина текста, но начальная_позиция плюс количество_знаков превышают длину текста, то функция ПСТР возвращает знаки вплоть до конца текста.

Примеры текстовых функций

	A	B	C
	Комаров	Михаил	Петрович
	Экономический	эф
	МГУ
		ЭФ			=ПРОПИСН(B2)
		мгу			=СТРОЧН(A3)
		Эф			=ПРОПНАЧ(В2)
		Ми			=ЛЕВСИМВ(В1;2)
		вич			=ПРАВСИМВ(С1;3)
	МГУЭкономический				=СЦЕПИТЬ(А3;A2)
	МГУ Экономический				=СЦЕПИТЬ(А3;" ";A2)
		М			=ПСТР(А3;1;1)
		Г			=ПСТР(А3;2;1)
		У			=ПСТР(А3;3;1)
	Комаров М.П.	=СЦЕПИТЬ(A1;" "ЛЕВСИМВ(B1);"."; ЛЕВСИМВ(C1);".")

=A1 & " " & ЛЕВСИМВ(B1) & "." & ЛЕВСИМВ(C1) &"."

2. Функция ВПР

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

Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

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

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

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

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

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

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

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

=ВПР( Искомое значение; таблица; №стл; ЛОЖЬ)

ПРИМЕРЫ:

	А	В	С	D	E	F
		Фамилия	Отдел	Оклад	Отпуск
		Иванов			Март
		Смирнов			Январь
		Семенов			Сентябрь
		Абрамов			Май

Пример 1:

Вывести оклад Семенова.

=ВПР( “Семенов”; $B$3:$E$6; 3; ЛОЖЬ) → 5400

Пример 2:

Является ли оклад Семенова максимальным?

=ЕСЛИ(ВПР(“Семенов”; $B$3:$E$6; 3; ЛОЖЬ) =МАКС($D$3:$D$6); "ДА"; "НЕТ") → НЕТ

Пример 3:

Определить фамилию работника, имеющего максимальный оклад.

Для решения этой задачи необходимо перестроить таблицу: поменять местами столбцы Оклад и Фамилия

=ВПР( Макс($B$3:$B$6); $B$3:$E$6; 3; ЛОЖЬ) → Смирнов

Вопрос:Для чего надо было менять местами столбцы Оклад и Фамилия?

САМОСТОЯТЕЛЬНАЯ. Функция ВПР и текстовые функции.

Используя функцию ВПР и необходимые текстовые функции, получите

следующую таблицу:

Решение:

=СЦЕПИТЬ($C3; $G3) =СЦЕПИТЬ($D3; " "; ЛЕВСИМВ($E3); "."; ЛЕВСИМВ($E3))

=ВПР( СЦЕПИТЬ($D15;$E$14); $A$3:$B$11; 2 ; ЛОЖЬ)

	A	B	C	D	E	F	G
…
				Факультет	Декан	НУЧ
				ММ	ЗЮЗИН А.Ю	КИРЕЕВ А.А
				ВМК	КОРЖИКОВ А.М	ЗАЦЕПИН А.С

=ВПР( СЦЕПИТЬ($D15;$F$14); $A$3:$B$11; 2 ; ЛОЖЬ)

Самостоятельная.

Подсчитать количество студентов в каждой группе на каждом факультете. Исходные данные на Листе 1. Отчет в указанной форме представить на Листе 2

Решение

	A	B	C	D	E
		Фамилия	Фак.	Гр.
		АЛЕКСЕЕВ А.В	ВМК		ВМК2	=СЦЕПИТЬ($C3; $D2)
		АРАКЕЛЯН А.В	ММ		ММ3
		АРСЕЕВ А.П	ММ		ММ2
		АБРАМОВ И.Х	ЭФ		ЭФ1
		БАКУЛИН А.В	ВМК		ВМК2
…		……………….	……..	…
		ДЕНИСОВ Н.А	ВМК		ВМК3

	A	B	C	D	Е
		Фак.	Гр.	Колич.	Общ.
		ВМК			39	(E3) =СУММ($D3:$D5)
			11	(D5) =СчетЕсли( Лист1!$E$3:$E$77; B3&C5)
		ММ
		ЭФ
Лист 2