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

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

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

ЗАДАЧА1: Решить уравнение ax²+bx+c=0 при а<>0

	A	B	C	D	E
	a =
	b =
	c =
	X1 =
	X2 =

		=ЕСЛИ (c1>=0; (-b2+c1^0,5)/(2*b1); “Нет решения”)
	=ЕСЛИ (c1>=0; (-b2-c1^0,5)/(2*b1); “Нет решения”)

ЗАДАЧА2: Для каждого отдела определить количество сотрудников и сумму денег для выплаты зарплаты. Данные приведены ниже.

	A	B	C	D	E	F	G	H	I
	Фамилия	Отдел	Оклад			Отдел	Количество	Сумма
	Алхазов
	Анисимов
	Артюхов
	Бабушкин
	Бадосов
	Барков
	Беженар

ЗАДАЧА3: По введенному году, месяцу, числу, определить день недели (понедельник, вторник, …, воскресенье)

	A	B	C	D	E	F
		Год	Месяц	День	День недели
					Пн
			22.8.2005

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=A1=N5 Ответ: ИСТИНА/ЛОЖЬ -не учитывает регистр.

► СОВПАД(A2;N7) Ответ: ИСТИНА/ЛОЖЬ -учитывает регистр.

► ДЛСТР(A1) -учитывает пробелы

Пример: А1 - Эком. Факультет =ДЛСТР(A1) Ответ: 15

► СЖПРОБЕЛЫ(A1) - удаляет начальные и конечные пробелы,

- внутри текста оставляет один пробел.

Пример: А1 - __Эком.___ Факультет_ =СЖПРОБЕЛЫ(A1)

Ответ: Эком. Факультет

► НАЙТИ (Искомый текст; Просматр.текст; [Начало поиска]) - учитывает регистр

- определяет позицию искомого текста

Примеры: А1 – Экономический факультет М ГУ

=НАЙТИ(“ M ”; A1) Ответ: 25

=НАЙТИ(“ куль ”; A1) Ответ: 17

=НАЙТИ(“”; A1) Ответ: 14

=НАЙТИ(“”; A1; Найти(“ ”; A1) +1) Ответ: 24

A5- Романов Владимир Николаевич

=СЦЕПИТЬ(ЛЕВСИМВ(A5;НАЙТИ(" ";A5)-1); " ";ПСТР(A5;НАЙТИ(" ";A5)+1;1); "."; ПСТР(A5; НАЙТИ(" ";A5; НАЙТИ(" ";A5)+1)+1;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

G

H

I

J

K

L

M

ПРИМЕРЫ:

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

Пример 1:

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

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

Пример 2:

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

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

Пример 3:

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

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

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

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

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

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

	A	B	C	D	E	F	G	H
			Факул.	Фамилия	Имя	Отчество	Должн.
			ММ	ДЕНИСОВ	Николай	Александрович
			ММ	ДОВОЛЬНОВ	Артем	Андреевич
			ВМК	ЗАЦЕПИН	Андрей	Сергеевич	ЗУР
			ММ	ЗЮЗИН	Алексей	Юрьевич	Декан
			ВМК	КАПАЛИН	Иван	Владимирович
			ММ	КИРЕЕВ	Алексей	Андреевич	ЗУР
			ММ	КОНДРАТЮК	Антон	Алексеевич
			ВМК	КОРЖИКОВ	Александр	Михайлович	Декан
			ММ	КОРНАЧЕВ	Иван	Андреевич

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

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

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

Решение:

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

	A	B	C	D	E	F	G
			Факул.	Фамилия	Имя	Отчество	Должн.
	ММ	ДЕНИСОВ Н.А	ММ	ДЕНИСОВ	Николай	Александрович
	ММ	ДОВОЛЬНОВ А.А	ММ	ДОВОЛЬНОВ	Артем	Андреевич
	ВМКНУЧ	ЗАЦЕПИН А.С	ВМК	ЗАЦЕПИН	Андрей	Сергеевич	НУЧ
	ММДекан	ЗЮЗИН А.Ю	ММ	ЗЮЗИН	Алексей	Юрьевич	Декан
	ВМК	КАПАЛИН И.В	ВМК	КАПАЛИН	Иван	Владимирович
	ММНУЧ	КИРЕЕВ А.А	ММ	КИРЕЕВ	Алексей	Андреевич	НУЧ
	ММ	КОНДРАТЮК А.А	ММ	КОНДРАТЮК	Антон	Алексеевич
	ВМКДекан	КОРЖИКОВ А.М	ВМК	КОРЖИКОВ	Александр	Михайлович	Декан
	ММ	КОРНАЧЕВ И.А	ММ	КОРНАЧЕВ	Иван	Андреевич

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

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

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

Дата добавления: 2014-12-29