Функция ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ предназначена для работы с текстовыми данными. Часто используется, когда необходимо подставлять значения в строку из другой ячейки.

Описание

Подставляет значение аргумента “нов_текст” вместо значения аргумента “стар_текст” в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст начиная с определенной позиции.

Что делает эта функция?

Функция ПОДСТАВИТЬ позволяет заменить все вхождения или N-ное вхождение подстроки в тексте на другой текст. Подстрокой может быть как один символ, так и несколько.

Функция является в некоторой степени аналогом процедуры Найти и Заменить в Excel, с некоторыми отличиями:

  • Что очевидно и является базовым отличием, она является функцией, т.е. пересчитывается автоматически, в отличие от одноразового характера процедур, и выводит результат в другую ячейку, в то время, как процедура меняет результат на месте;
  • Функция ПОДСТАВИТЬ всегда чувствительна к регистру. В процедуре «найти и заменить» чувствительность к регистру опциональна;
  • Функция не поддерживает подстановочные символы («*» и «?»);
  • Зато позволяет осуществлять массовую замену нескольких (до 64 в последней версии Excel) значений одновременно;
  • Еще одно преимущество функции в том, что она позволяет заменить не все вхождения, а определенное по порядку в строке, чего не позволяет сделать процедура.

Что возвращает функция

Она возвращает текстовую строку, где старый текст заменен на новый.

1. МАКС

  • Синтаксис: =МАКС(число1; [число2]; …).

Формула «МАКС» отображает наибольшее из чисел в выбранных ячейках. Аргументами функции могут выступать как отдельные ячейки, так и диапазоны. Обязательно вводить только первый аргумент.

Синтаксис

=ПОДСТАВИТЬ(Ячейка_или_текст;Заменяемый_текст;Чем_заменяем;[Порядковый_номер_заменяемого])

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

Синтаксис

=SUBSTITUTE(text, old_text, new_text, [instance_num]) – английская версия

=ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения]) – русская версия

Дисперсия выборки

Дисперсия выборки ( выборочная дисперсия, sample variance ) характеризует разброс значений в массиве относительно среднего .

stat-23.png

Все 3 формулы математически эквивалентны.

Из первой формулы видно, что дисперсия выборки это сумма квадратов отклонений каждого значения в массиве от среднего , деленная на размер выборки минус 1.

В MS EXCEL 2007 и более ранних версиях для вычисления дисперсии выборки используется функция ДИСП() , англ. название VAR, т.е. VARiance. С версии MS EXCEL 2010 рекомендуется использовать ее аналог ДИСП.В() , англ. название VARS, т.е. Sample VARiance. Кроме того, начиная с версии MS EXCEL 2010 присутствует функция ДИСП.Г(), англ. название VARP, т.е. Population VARiance, которая вычисляет дисперсию для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у ДИСП.В() , у ДИСП.Г() в знаменателе просто n. До MS EXCEL 2010 для вычисления дисперсии генеральной совокупности использовалась функция ДИСПР() .

Дисперсию выборки можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1) =(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула =СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) – формула массива

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

Дисперсия выборки является точечной оценкой дисперсии распределения случайной величины, из которой была сделана выборка . О построении доверительных интервалов при оценке дисперсии можно прочитать в статье Доверительный интервал для оценки дисперсии в MS EXCEL .

Дисперсия случайной величины

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

Для дисперсии случайной величины Х часто используют обозначение Var(Х). Дисперсия равна математическому ожиданию квадрата отклонения от среднего E(X): Var(Х)=E[(X-E(X)) 2 ]

Если случайная величина имеет дискретное распределение , то дисперсия вычисляется по формуле:

stat-179.png

где x i – значение, которое может принимать случайная величина, а μ – среднее значение ( математическое ожидание случайной величины ), р(x) – вероятность, что случайная величина примет значение х.

Если случайная величина имеет непрерывное распределение , то дисперсия вычисляется по формуле:

stat-180.png

где р(x) – плотность вероятности .

Для распределений, представленных в MS EXCEL , дисперсию можно вычислить аналитически, как функцию от параметров распределения. Например, для Биномиального распределения дисперсия равна произведению его параметров: n*p*q.

Примечание : Дисперсия, является вторым центральным моментом , обозначается D[X], VAR(х), V(x). Второй центральный момент – числовая характеристика распределения случайной величины, которая является мерой разброса случайной величины относительно математического ожидания .

Примечание : О распределениях в MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Размерность дисперсии соответствует квадрату единицы измерения исходных значений. Например, если значения в выборке представляют собой измерения веса детали (в кг), то размерность дисперсии будет кг 2 . Это бывает сложно интерпретировать, поэтому для характеристики разброса значений чаще используют величину равную квадратному корню из дисперсии – стандартное отклонение .

Некоторые свойства дисперсии :

Var(Х+a)=Var(Х), где Х – случайная величина, а – константа.

Var(aХ)=a 2 Var(X)

Var(Х)=E[(X-E(X)) 2 ]=E[X 2 -2*X*E(X)+(E(X)) 2 ]=E(X 2 )-E(2*X*E(X))+(E(X)) 2 =E(X 2 )-2*E(X)*E(X)+(E(X)) 2 =E(X 2 )-(E(X)) 2

Это свойство дисперсии используется в статье про линейную регрессию .

Var(Х+Y)=Var(Х) + Var(Y) + 2*Cov(Х;Y), где Х и Y – случайные величины, Cov(Х;Y) – ковариация этих случайных величин.

Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе стандартной ошибки среднего .

Покажем, что для независимых величин Var(Х-Y)=Var(Х+Y). Действительно, Var(Х-Y)= Var(Х-Y)= Var(Х+(-Y))= Var(Х)+Var(-Y)= Var(Х)+Var(-Y)= Var(Х)+(-1) 2 Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения доверительного интервала для разницы 2х средних .

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Сведения о продажах

<

Квартал 1, 2008 г.

<

Квартал 1, 2011 г.

<

Формула

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

Результат

=ПОДСТАВИТЬ(A2; “продажах”; “ценах”)

Замена “ценах” на “продажах” (“Сведения о ценах”)

Сведения о ценах

=ПОДСТАВИТЬ(A3; “1”; “2”; 1)

Замена первого экземпляра “1” на “2” (“Квартал 2, 2008 г.”)

Квартал 2, 2008 г.

=ПОДСТАВИТЬ(A4; “1”; “2”; 3)

Замена третьего экземпляра “1” на “2” (“Квартал 1, 2012 г.”)

Квартал 1, 2012 г.

Примеры

Функция ПОДСТАВИТЬ необычайно популярна и может использоваться:

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

Рассмотрю несколько примеров ситуаций, в которых лично использовал функцию.

ПОДСТАВИТЬ с несколькими условиями (транслитерация)

Заменить кириллицу на транслит автоматически с помощью формулы? Можно! Правда, длина формулы настолько велика, что ее лучше сохранить где-нибудь, а не составлять самостоятельно. Потому что это формула с 64 условиями (уровнями вложенности). Подробнее — по ссылке в начале этого абзаца.

ПОДСТАВИТЬ, чтобы посчитать слова

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

Сколько слов в ячейке? На 1 больше, чем пробелов между ними. Это будет справедливо всегда, если разделителем слов считается только пробел, и если пробелов между каждыми двумя словами не более 1. Чтобы обезопасить себя от лишних, можно воспользоваться функцией СЖПРОБЕЛЫ.

Как посчитать пробелы в строке? Как и любой символ, можно посчитать их, подсчитав длину строки с ними и без них, и вычислив разницу. Как получить строку без них? Конечно, с помощью функции ПОДСТАВИТЬ.

Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. ДЛСТР измеряет длину строк:

=ДЛСТР(СЖПРОБЕЛЫ(A1)) – ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);” “;””)) + 1

У формулы есть побочный эффект — для пустой ячейки она возвращает 1, поэтому, если пустые ячейки могут присутствовать, формулу выше придется «обернуть» выражением с Excel функцией ЕСЛИ:

=ЕСЛИ(A1=””;0;ДЛСТР(СЖПРОБЕЛЫ(A1)) – ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);” “;””)) + 1)

Заменить пробелы на переносы строк

Часто возникает задача заменить пробелы в строке на переносы строк, и наоборот. Здесь будет полезна комбинация функций ПОДСТАВИТЬ и СИМВОЛ. Так будет выглядеть формула, заменяющая все пробелы в строке на переносы строк.

=ПОДСТАВИТЬ(A1;” “;СИМВОЛ(10))

Как видно из формулы, код символа переноса строки — 10.

Обратите внимание, что результат может отображаться сплошной строкой, если не включить опцию «Перенести текст».

Отображение переносов строк в Excel

ПОДСТАВИТЬ, чтобы посчитать встречаемость в тексте

Узнать, сколько раз встречается фрагмент в тексте, можно довольно просто с помощью комбинации функций ПОДСТАВИТЬ и ДЛСТР.

  1. Первая (ПОДСТАВИТЬ) позволяет создать строку без указанного фрагмента, заменяя его на пустоту.
  2. Вторая (ДЛСТР) — измерить длину результата.
  3. Если вычесть из исходной длины полученную, и разделить на длину этого фрагмента, можно получить его встречаемость.
  4. Если заведомо известно, что это один символ, процесс деления можно опустить, т.к. результат от деления на 1 не изменится.

Так выглядит формула, считающая в ячейке A1 количество 1-символьного фрагмента из ячейки B1:

=ДЛСТР(A1) – ДЛСТР(ПОДСТАВИТЬ(A1;B1;””))

Так — для подсчета встречаемости конкретного символа в ячейке, в данном случае — косой черты, что позволяет, например, посчитать уровень вложенности директории по ее адресу:

=ДЛСТР(A1) – ДЛСТР(ПОДСТАВИТЬ(A1;”/”;””))

А так — для фрагмента длиной более 1 символа, например, если нужно посчитать, сколько раз встречается конкретное слово в тексте:

=(ДЛСТР(A1) – ДЛСТР(ПОДСТАВИТЬ(A1;B1;””))) / ДЛСТР(B1)

ПОДСТАВИТЬ для подсчета цифр в тексте

Представим, что наша задача — провалидировать список номеров телефонов на количество цифр в нем. Корректным будем считать номер, в котором 11 цифр.

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

Очевидно, что функция ДЛСТР не подойдет в таком случае, как и не удастся сходу удалить все лишние символы, чтобы оставить в ячейках только цифры.

Комбинация функций ПОДСТАВИТЬ и ДЛСТР в формуле массива с функцией СУММ позволит решить данную задачу:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};””)))

Подсчитываем количество цифр в строках со смешанным содержимым. В столбце B формулы, в столбце С — их текст.

См. также

Функция ЗАМЕНИТЬ, ЗАМЕНИТЬБ

Функция СЖПРОБЕЛЫ

Нужна дополнительная помощь?

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...