Как в excel массово найти и заменить несколько значений на другие

Инструменты поиска

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

В первую очередь необходимо указать место, которое можно задать в категории «Искать», в зависимости от указанного диапазона поиск может производиться как на определённом листе, так и во всей книге.

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

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

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

Юзер должен быть готовым к тому, что посредством поиска будет просмотрен весь указанный диапазон ячеек, программа последовательно найдёт и выделит каждое совпадение. Просмотреть всё найденное можно поэтапно, передвигаясь по ячейкам с нужным содержимым за один клик. «Найти всё» позволит отобразить в документе список, в котором содержатся адреса абсолютно всех совпадений.

Поиск

Чтобы найти что-то, нажмите CTRL+F или перейдите на главная > редактирования> Найти & Выберите >Найти.

Примечание: В следующем примере мы щелкнули> параметры поиска , чтобы отдемонстрировать все диалоговое окно Найти. По умолчанию он будет отображаться со скрытыми параметрами поиска.

1. В поле Найти: введите текст или числа, которые нужно найти.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

   Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти «м?т», то будут «ед» и «множество».

   Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки «г*д» и «начало», они будут отозныны.

   Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

2. Нажмите кнопку Найти далееили Найти все, чтобы выполнить поиск.

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

3. Нажмите > параметры поиска , чтобы при необходимости определить поиск.

   • В: Чтобы найти данные в пределах определенного выделения, выберите выделение. Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

   • Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

   • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

   • Совпадение со всем содержимым ячеек: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти.

Примеры применения ДЛСТР

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

К тому же эти комбинации позволяют решать самые необычные задачи.

Пример 1 — посчитать слова

Сколько слов в ячейке? Обычно между ними на 1 больше пробелов. Если есть пробел, значит два слова, если два, то три слова и так далее.

Если есть вероятность, что между словами больше 1 пробела или есть пробелы в начале и конце ячейки, функция TRIM может удалить их.

Пробелы в строке можно подсчитать, измерив длину строки в символах:

• с ними,
• без них,
• и вычисляем разницу.

Как получить строку без пробелов? Здесь поможет функция REPLACE.

Так будет выглядеть формула для ячейки А1 с учетом всех этих нюансов. TRIM удаляет лишние пробелы, SUBSTITUTE удаляет их, а DLSTR измеряет длину строк:

= DLSTR (A1) — DLSTR (ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1), «»; «»)) + 1

Пример 2 — посчитать встречаемость фрагмента в тексте

Есть ли в тексте какой-то конкретный персонаж? И если да, то сколько их?

Давайте взглянем на пару примеров формул.

Есть ли в ячейке лишние пробелы

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

= DLSTR (ОБРЕЗАТЬ (A1)) <> DLSTR (A1)Формула для проверки наличия лишних пробелов в строке

Формула очевидна и достаточно проста. Если длина ячейки после удаления лишних пробелов с помощью функции ОБРЕЗАТЬ такая же, как у исходной, значит, их нет, а если, наоборот, не то же самое, то есть.

Обратите внимание, что несмотря на то, что в том же утверждении, что и вопрос, звучит условие ЕСЛИ, функция ЕСЛИ здесь не нужна. Если равенство верное, Excel поместит результат ИСТИНА в саму ячейку и ЛОЖЬ, если наоборот

Сколько раз встречается символ или слово в тексте

По аналогии с предыдущим примером мы не можем выполнить логическое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строк до и после удаления символа или фрагмента, а вычислить разницу.

Если мы ищем персонажа, он сразу поймет его присутствие.

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

Подсчитайте появление буквы в ячейке

Если фрагмент текста состоит из 2 и более символов, нам также нужно разделить результат на его длину:

= (DLSTR (A1) -LSTR (ПОДСТАВИТЬ (A1; B1; «»))) / DLSTR (B1)

В этой формуле исходный текст находится в ячейке A1, а подсчитываемый кусок — в ячейке B1.

Подсчитать появление фрагмента текста

Обратите внимание на строки 5 и 6: поскольку функция «ПОДСТАВИТЬ» чувствительна к регистру, она не удаляла первое вхождение из текста. Если вам все еще нужно учитывать вхождения, вам могут понадобиться функции LOWER или UPPERCASE

Подсчитываемый текстовый фрагмент может быть любой длины.

Еще один момент: функция может не подходить для подсчета коротких слов, потому что одно слово может входить в состав другого.

Пример 3 — извлечь или удалить последнее слово

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

В этом, как и в предыдущем примере, нам поможет функция REPLACE: она позволяет заменять не только все определенные фрагменты текста в строке, но и конкретное вхождение (первое, второе и т.д.).

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

Пример 4 — удалить первые N символов / первое слово

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

Механика проста:

1. вычислить позицию первого пробела с функцией ПОИСК,
2. вычтите это число из длины строки (DLSTR
3. Извлеките функцию RIGHT, полученную в результате отличия от исходной строки

Убираем первое слово из ячейки, получая второе

Более подробно пример рассмотрен в статье о функции ВПРАВО.

Если количество символов заранее известно, формула еще проще: нужны только DLSTR и RIGHT:

= ВПРАВО (A1; DLSTR (A1) -4)Удалите первые 4 символа из строки с помощью функций DLSTR и RIGHT

Пример 5 — замена последнего символа в ячейке

Это может показаться очевидным, но позиция последнего символа в строке равна его длине в символах. А затем, зная длину строки, вы можете заменить последний символ в ней, взяв на 1 символ меньше, используя ВЛЕВО, и объединив с желаемым текстом через амперсанд:

= LEFT (A1; DLSTR (A1) -1) & «Текст вместо последнего символа»

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

= REPLACE (A1; DLSTR (A1); 1; «Текст вместо последнего символа»)

Логические функции Excel

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

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2. ) =ИЛИ(логическое_значение1;логическое_значение2. )

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА() =ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение «Проходите», если выражение в ячейке А1 имеет значение ИСТИНА:

В противном случае формула возвратит «Стоп».

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

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

В начало страницы

В начало страницы

Добавление функции ЗНАЧЕН

Теперь модернизируем формулу, чтобы в итоге получить числовое значение, а не текст, как в исходном варианте. Для этого понадобится функция ЗНАЧЕН, которую просто нужно вписать в строку, поставив дополнительные кавычки.

Выполните редактирование, чтобы в итоге получить примерно такой результат: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;».»;»,»)).

Нажмите Enter для применения изменений и обратите внимание на то, что теперь числа отображаются справа в ячейке, что говорит об успешной смене формата с текстового на числовой.

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

Посмотрите на таблицу и удалите лишнюю информацию, которая после преобразования больше не понадобится.. Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел

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

Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел. Теперь вы узнали, что решить проблему можно при помощи всего одной формулы, состоящей из двух функций. Используйте это, чтобы оптимизировать взаимодействие с электронной таблицей.

Создание условной формулы, которая приводит к логическому значению (истина или ложь)

Для выполнения этой задачи используйте функции и операторы and, orи Not , как показано в следующем примере.

Пример

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

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов

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

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

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

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

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2. ) =ИЛИ(логическое_значение1;логическое_значение2. )

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

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

Поставим в экселе знак больше или равно.

Перед нами таблица, в которой есть числа от 1 до 10. В рядом стоящем столбце, нужно прописать функцию «ЕСЛИ», с условием, что если цифра больше или равно трем ставиться «Да», а если условие не выполняется, то ставиться «Нет».

В ячейке «В2» пропишем формулу: =ЕСЛИ(A2>=3;»Да»;»Нет»), где знак >= означает больше или равно. Копируем данную формулу на все ячейки.

Поставим в экселе знак меньше или равно.

Поменяем задачу, теперь поставим «Да», если числом меньше или равно трем, а при невыполнении условия ставим слово «Нет». Для этого в ячейке «В2» пишем формулу =ЕСЛИ(A2 3;»Нет»;»Да»), где знаком мы обозначаем неровно трем.

Функция НАЙТИ

Возвращает число, являющееся появлением первого символа подстроки искомого текста. Если текст не найден, возвращается ошибка «# ЗНАЧ!».

Синтаксис: = НАЙТИ (текст_поиска; текст_поиска; начальная_позиция)

Определения аргументов:

• search_text — строка для поиска;
• text_for_search — текст, в котором ищется первый аргумент;
• start_position не является обязательным. Принимает целое число, указывающее начальный символ search_text. По умолчанию 1.

Пример использования:

Из отрывка из стихотворения великого поэта С.А. Есенина находим появление первого символа строки «птица». Поиск ведется с начала строки. Если в данном примере поиск производился по 40 символам, то в результате функция вернет ошибку, так как позиция совпадения не найдена.

Метод 6: функция СЧИТАТЬПУСТОТЫ

В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.

По синтаксису функция крайне проста:

=СЧИТАТЬПУСТОТЫ(диапазон)

Порядок действий практически ничем не отличается от вышеперечисленных:

1. Выбираем ячейку, куда хотим вывести итоговый результат по подсчету количества пустых ячеек.
2. Заходим в Мастер функций, среди статистических операторов выбираем “СЧИТАТЬПУСТОТЫ” и нажимаем ОК.
3. В окне «Аргументы функции» указываем нужный диапазон ячеек и кликаем по кнопку OK.
4. В заранее выбранной нами ячейке отобразится результат. Будут учтены исключительно пустые ячейки и проигнорированы все остальные.

Функция ПСТР

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

Синтаксис функции:

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

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

Пример применения:

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

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

Как можно использовать функцию SUBSTITUTE (ПОДСТАВИТЬ)?

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

• Изменять название региона в заголовке отчёта.
• Удалить непечатаемые символы.
• Заменить последний символ пробела.

Синтаксис SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) имеет вот такой синтаксис:

• text (текст) – текстовая строка или ссылка, где будет осуществлена замена текста.
• old_text (стар_текст) – текст, который надо заменить.
• new_text (нов_текст) – текст, который будет вставлен.
• instance_num (номер_вхождения) – номер вхождения текста, который нужно заменить (не обязательный аргумент).

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом – спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами – заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM). Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Примеры использования функции ПОДСТАВИТЬ

Несколько интересных примеров рассмотрены на видео и в прикреплённом файле Excel, который можно скачать после статьи. Посмотрев видео-урок, скачайте файл и поэкспериментируйте самостоятельно с различными видами замен в тексте.

Давайте сделаем примитивную замену, чтобы быстро проверить функцию в работе. Скопируйте в любую ячейку своей таблицы следующее выражение: «=ПОДСТАВИТЬ(«Добрый день, Вася!»;»день»;»вечер»)». После нажатия Enter мы получим такой текст: «Добрый вечер, Вася!». Другие примеры, более сложные и интересные, смотрите в файле.

Стоит заметить, что возможность автоматической массовой обработки с выполнением множества замен значительно расширяет возможности программы по автоматизированной обработке импортированного текста.

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

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

При замене определенного текста функцию REPLACE () неудобно использовать. Намного удобнее использовать функцию REPLACE() .

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

= ЗАМЕНИТЬ (A2, 10, 6, «Февраль») = ЗАМЕНИТЬ (A2, «Январь», «Февраль»)

те функции REPLACE (), которые необходимы для вычисления начальной позиции слова Январь (10) и его длины (6). Это не удобно, функция REPLACE () намного проще справляется с задачей.

Кроме того, функция REPLACE () заменяет, по очевидным причинам, только одно вхождение строки, функция SUBSTITUTE () может заменять все вхождения или только первое, только второе и т.д. Давайте объясним это на примере. Вставьте строку Продажи (январь), Прибыль (январь) в ячейку A2. Записываем формулы: = REPLACE (A2; 10; 6; «Февраль») = REPLACE (A2; «Январь»; «Февраль») получаем в первом случае строку Продажи (Февраль), прибыль (Январь), в второй — Продажи (февраль), прибыль (февраль). Записывая формулу = ПОДСТАВИТЬ (A2; «Январь»; «Февраль»; 2) получаем строку Продажи (январь), прибыль (февраль) .

Кроме того, функция REPLACE () чувствительна к REGISTER. Написав = ПОДСТАВИТЬ (A2; «ЯНВАРЬ»; «ФЕВРАЛЬ») мы получим строку без изменений Продажи (январь), прибыль (январь), так как для функции ПОДСТАВИТЬ () «ЯНВАРЬ» не то же самое, что «Январь».

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

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

Все 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 .

Трудности с конфигурацией чисел на дату

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

1. Попытайтесь ввести какое-нибудь число с точкой. К примеру, «1.1».

1. Нажмите на кнопку Enter.
2. В итоге вы увидите, что в ячейке число «1.1» поменялось на «01.янв».

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

1. Сперва делаем активной какую-нибудь клеточку.
2. Потом кликаем по ней.
3. Опосля этого избираем пункт «Формат ячеек».

Или просто нажмите на жаркие клавиши Ctrl+1. В обоих вариантах итог будет однообразный.

1. В показавшемся окне необходимо указать или числовой, или текстовый формат. Другими словами ваше значение быть может цифрой либо обыкновенной строкой. В этом случае автозамена на дату происходить не будет.
2. Для сохранения конфигураций нажмите на клавишу «OK».

1. Попытайтесь снова ввести какое-нибудь числовое значение с внедрением точки. Вы увидите, что никаких конфигураций не произойдет.

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