Как найти ячейку в excel по содержанию: Как найти столбец в «Excel» или поиск потеряного столбца

Содержание

Как в экселе найти нужное слово: 100% сопобы!

Всем добра друзья! И сегодня вы узнаете как в экселе найти нужное слово. Зачастую работа с Excel подразумевает существенные информационные объемы. В подобных файлах разрабатываются поистине громадные таблицы с тысячами строчек, столбиков, а также, позиций. Найти в таком огромном массиве какие-либо конкретные значения порой бывает невероятно тяжело.

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

Как в экселе найти нужное слово по ячейкам

Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:

Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
В поле следует пропечатать фразу, искомые слова либо цифры.
Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
Кликнув на любую строчку, будет выделена соответствующая ячейка.
С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».

Поисковые параметры

Возможно задавать собственные условия. К примеру, осуществлять поиск на основании нескольких знаков. Вот каким образом отыскать слово, которое вы не помните полностью:

Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А????», будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».

Кроме того, вы можете посещать настройки:

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

Настройки форматирования ячеек

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

В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.

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

Как найти несколько слов в Excel

В файлах Эксель можно искать ячейки по целым выражениям. Однако, введя ключ, к примеру «Красный шар», системой будет выполняться работа конкретно по данному запросу. Результаты не отобразят значения, имеющие вид «Красный сверкающий шар» либо «Красный яркий шар».

Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:

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

Применяем фильтр

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

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

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

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

Как в Excel сделать выпадающий список (простой или зависимый) с выбором нескольких значений?

Доброго времени на fast-wolker.ru! В процессе рутинной работы с таблицами всегда возникает желание ускорить ввод значений. Особенно это касается наименований позиций. Зачастую мы имеем один и тот же список, с которыми постоянно приходится работать, а новые добавляются постепенно.

Каждый раз заносить в таблицу одно и то же название утомительно; к тому же всегда есть возможность ошибки — поставить лишнюю точку, пробел и так далее. Сегодня расскажу о способах, который позволяет в ряде случаев ускорить работу. В самых простых примерах, может что-то подойдет и Вам. Итак…

Как в экселе создать выпадающий список с выбором нескольких значений

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

Ставим курсор на следующую пустую ячейку под списком кликаем правой кнопкой мыши; из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

Лучше использовать следующий способ — хранение готового списка на другом листе нашей книги. А в нужной ячейке делать ссылку на данные этого листа.

Как в эксель сделать выпадающий список с другого листа?

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

Выделяем нужную область и в верхней левом верхнем углу присваиваем ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы потом диапазонами управлять, нужно открыть вкладку «Формулы» и найти там «Диспетчер имен»:

Следующий шаг — идем обратно в наш лист где создана основная таблица. Выделяем в графе нужные строки. Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

А в строке «Источник» копируем адрес нашего диапазона из «диспетчера имен»…

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

Как в экселе сделать выпадающий список в ячейке с выбором нескольких данных

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

Сейчас в них немного позиций; ниже будет описано, как сделать, чтобы при добавлении в «базу» новых позиций они автоматом появлялись в списке при выборе. Первым сделаем выпадающий список категорий в точности так, как это было описано в предыдущем примере. Идем в «Данные -«Проверка данных». Укажем в качестве диапазона только строку с названиями категорий.

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

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

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула» распространится корректно.

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

Делаем выпадающий список в ячейке эксель с выбором по первой букве

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

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

Курсор мыши приобретет форму крестика.

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

ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
ListRows — количество отображаемых строк в списке;
Font — выбираем шрифт, размер, стиль;
ForeColor и BackColor — цвет текста и фона;
ListRow — количество возвращаемых строк в списке;
PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Автор публикации

не в сети 1 неделя

admin

0 Комментарии: 61Публикации: 386Регистрация: 04-09-2015
Как найти номер строки и столбца в Excel? Функция строка и функция столбец в Эксель
Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифры
Номер строки и столбца в Excel может пригодиться в разных ситуациях. Функции могут использоваться для создания нумерации строк списка или таблицы, их можно использовать и в формулах, и в условном форматировании. Так же разберем как быстро посчитать количество столбцов без формул.
Функция СТРОКА() и СТОЛБЕЦ(). Как использовать?
Интересная особенность этих формул, что они могут работать как с реквизитами, так и без них. Т.е. формула =СТРОКА() будет возвращать номер именно этой строки, а =СТРОКА(A3) будет возвращать номер строки ячейки A3, соответственно 3.
Подробнее на примере
Номер строки и столбца для списка или таблицы
Довольно часто функция СТРОКА используется для нумерации данных в списке, достаточно только сопоставить номер строки и номер позиции списка, в данном случае делаем -1
Очень удобно, если вы часто изменяете порядок строки, удаляете или добавляете их. Поскольку формула остается, то порядок всегда будет правильным.

Тоже самое можно провернуть для нумерации столбцов.
Как посчитать количество столбцов в выделенном диапазоне столбцов?
Если вы выделяете не диапазон ячеек, а диапазон, к примеру столбцов, необязательно пользоваться какой-то формулой, чтобы посчитать сколько столбцов в диапазоне. При выделение диапазона, количество столбцов (еще раз обращу внимание, если вы выделяете именно диапазон столбцов) автоматически считается и показывается. Буква С означает, что это считаются колонки -Colomn
Это очень удобно, когда вы создаете формулу с функцией ВПР.
Соответственно, если вы выделяете диапазон строк, то будет отображаться число строк.
Функция СТРОКА() в условном форматировании. Как сделать зебру в таблице — чередование цветов заливки?
Условным форматировании можно раскрасить заливку ячеек и даже целых таблиц. Удобно для чтения, когда каждая строка таблица выделяется чередующимся цветом — т.н. зеброй. Как раз функция СТРОКА() нам и поможет.

Задайте в окне формул условного форматирования:
=НЕЧЁТ(СТРОКА())=СТРОКА()
Выберете цвет

И получилось удобное оформление
Я довольно часто пользуюсь этим, т.к. не очень люблю форматирование в виде Таблицы.

Пример файла Номер строки и столбца

Поделитесь нашей статьей в ваших соцсетях:
Похожие статьи
Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифры
15 функций анализа данных Excel, которые необходимо знать
Если вы когда-либо использовали Excel, то, вероятно, испытали агонию выбора неправильной формулы для анализа набора данных. Возможно, вы работали над этим часами, и в конце концов сдались, потому что вывод данных был неправильным или функция была слишком сложной, и казалось, что проще подсчитать данные самостоятельно вручную. Если это похоже на вас, то этот анализ данных в топ-15 Excel для вас.

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

Независимо от того, занимаетесь ли вы Excel или активно пользуетесь им в своей работе, в этом списке найдется функция для каждого.
1. СЦЕПИТЬ

= CONCATENATE — одна из самых простых в освоении, но наиболее эффективных формул при анализе данных. Объединяйте текст, числа, даты и многое другое из нескольких ячеек в одну.Это отличная функция для создания конечных точек API, артикулов продуктов и запросов Java.

Формула:
= СЦЕПИТЬ (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ВЫ ХОТИТЕ ОБЪЕДИНЯТЬ)

В этом примере:

= СЦЕПИТЬ (A2; B2)
2. LEN

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

Формула:
= ОБЪЕКТИВ (ВЫБОР ЯЧЕЙКИ)

В этом примере:
= LEN (A2)
3. COUNTA
= COUNTA определяет, пуста ячейка или нет. В жизни аналитика данных вы будете ежедневно сталкиваться с неполными наборами данных. COUNTA позволит вам оценить любые пробелы в наборе данных без реорганизации данных.

Формула:
= СЧЁТ (ВЫБОР ЯЧЕЙКИ)

В примере:

= СЧЕТ (A10)
4. ДНИ / ЧИСТЫЕ ДНИ

= ДНИ — это именно то, что подразумевается. Эта функция определяет количество календарных дней между двумя датами. Это полезный инструмент для оценки жизненного цикла продуктов, контрактов и оценки доходов в зависимости от продолжительности услуг — важен анализ данных.

= NETWORKDAYS немного более надежен и полезен.Эта формула определяет количество «рабочих дней» между двумя датами, а также возможность учета праздников. Даже трудоголикам время от времени нужен перерыв! Использование этих двух формул для сравнения временных рамок особенно полезно для управления проектами.

Формулы:
= ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ)
ИЛИ
= ЧИСТЫЕ ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ, [количество праздников])
примечание: [количество праздников] необязательно

В примере:
= ДНИ (C8, B8)
ИЛИ
= ЧИСТЫЕ ДНИ (B7, C7,3)
5. СУММЕСЛИМН

= СУММЕСЛИМН — одна из формул, которую необходимо знать аналитику данных. Обычно используется формула = СУММ, но что, если вам нужно суммировать значения на основе нескольких критериев? СУММЕСЛИМН. В приведенном ниже примере СУММЕСЛИМН используется для определения того, какой вклад каждый продукт приносит в выручку.

Формула:
= СУММЕСЛИ (ДИАПАЗОН; КРИТЕРИИ; [диапазон_суммы])
примечание: [диапазон_суммы] является необязательным

В примере:
= СУММЕСЛИ ($ B $ 2: $ B $ 28, $ A $ 2: $ A $ 28, $ F2)
6.СРЕДНЕНОМН

Подобно СУММЕСЛИМН, СРЕДНЕЛИМН позволяет брать среднее значение на основе одного или нескольких критериев.

Формула:
= СРЕДНЕЛИ (ВЫБРАТЬ ЯЧЕЙКУ, КРИТЕРИИ; [СРЕДНИЙ_ДИАПАЗОН])
примечание: [средний_ диапазон] является необязательным

В примере:
= СРЕДНЕЛИ ($ C: $ C, $ A: $ A, $ F2)
7. ВПР
VLOOKUP — одна из самых полезных и узнаваемых функций анализа данных. Как пользователю Excel, вам, вероятно, в какой-то момент понадобится «объединить» данные.Например, отдел дебиторской задолженности может знать, сколько стоит каждый продукт, но отдел отгрузки может предоставить только отгруженные единицы. Это идеальный вариант использования ВПР.

На изображении ниже мы используем справочные данные (A2) в сочетании с таблицей цен, чтобы Excel находил критерии соответствия в первом столбце и возвращал соседнее значение.

Формула:
= ВПР (LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP])

Скрытие столбцов на основе значения ячейки (Microsoft Excel)
Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003.Если вы используете более позднюю версию (Excel 2007 или более позднюю), , этот совет может вам не подойти . Чтобы ознакомиться с версией этого совета, написанного специально для более поздних версий Excel, щелкните здесь: Скрытие столбцов на основе значения ячейки.
Отличные возможности условного форматирования
Excel позволяют изменять форматирование ячеек в зависимости от содержимого ячейки.К сожалению, нет способа легко скрыть целые столбцы данных на основе значения определенной ячейки.
Однако вы можете добиться желаемого эффекта, используя макрос для анализа ячейки и настройки атрибута Hidden для строки, которую вы хотите условно скрыть. Следующий простой макрос, например, проверяет содержимое ячейки B4 и, если ячейка содержит 0, скрывает столбец H. Если ячейка B4 не содержит 0, отображается столбец H.
Sub HideColumn1 () Если Range ("B4").Значение = 0 Тогда Столбцы ("H"). CompleteColumn.Hidden = True Еще Столбцы ("H"). CompleteColumn.Hidden = False Конец, если Конец подписки
Если вы хотите, чтобы скрытие и отображение столбца выполнялось в реальном времени, вы можете использовать следующую версию макроса. Просто убедитесь, что вы поместили эту версию в окно кода для рабочего листа, на котором вы хотите, чтобы она работала.
Private Sub Worksheet_SelectionChange (цель ByVal как диапазон) Если Range ("B4").Значение = 0 Тогда Столбцы ("H"). CompleteColumn.Hidden = True Еще Столбцы ("H"). CompleteColumn.Hidden = False Конец, если Конец подписки
Обратите внимание, что внутренности этих двух макросов одинаковы. Единственное отличие состоит в том, что вторая версия запускается событием в Excel — изменением ячейки, выбранной в данный момент. Это означает, что каждый раз, когда вы переходите из одной ячейки в другую, значение в B4 проверяется, а столбец H либо скрыт, либо не скрыт.
Если возможно, что содержимое ячейки B4 может быть пустым, то возможно, что Excel интерпретирует эту пустоту как нулевое значение. В этом случае вы можете немного изменить макрос, чтобы он проверял наличие пустой ячейки.
Sub HideColumn2 () Диапазон Dim rCell As Установите rCell = Range ("B4") Столбцы ("H"). CompleteColumn.Hidden = False Если (Not IsEmpty (rCell)) И (IsNumeric (rCell) И (rCell.Value = 0) Тогда Столбцы («H»).CompleteColumn.Hidden = True Конец, если Конец подписки
Эта версия макроса фактически проверяет три условия: что B4 не пусто, что он содержит числовое значение и что значение равно 0. Если все три из этих условий выполнены, то столбец H скрыт.
ExcelTips — ваш источник экономичного обучения работе с Microsoft Excel. Этот совет (3283) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Скрытие столбцов на основе значения ячейки .
Автор Биография
Аллен Вятт
Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …
Связывание документов Word
Хотите добавить один документ к другому? Вы можете сделать это, добавив ссылки, описанные в этом совете.
Узнайте больше
Установка атрибутов по умолчанию для линий и стрелок
Не нравится, как Excel форматирует линии и стрелки? Вы можете легко внести свои собственные изменения в форматирование, а затем использовать …
Узнайте больше
Определение комбинаций для получения общей суммы
Если у вас есть диапазон ячеек, содержащих значения, вы можете задаться вопросом, какие комбинации этих ячеек следует использовать для соответствия . ..
Узнайте больше
Как найти выбросы в Excel
Excel имеет множество недостаточно используемых функций, которые могут значительно улучшить анализ данных.Одна из лучших функций — это статистические возможности. Вы можете легко находить выбросы в Excel с помощью простых статистических формул.
Почему вы должны изолировать и устранять выбросы в ваших данных? Одна из основных причин заключается в том, что эти крайние значения данных будут искажать ваши результаты, что часто приводит к ошибочным предположениям. По сути, это будет перетаскивать ваше среднее значение в положительном или отрицательном направлении. Удаление этих выбросов даст вам лучшее представление о ваших данных.
Для определения выбросов в Excel можно использовать как визуализации, так и формулы.Давайте начнем со статистики, чтобы найти выброс в Excel. Посмотрите отличный курс Master Excel от новичка до продвинутого, чтобы быстро улучшить свои навыки.
Вот статистические концепции, которые мы будем использовать для поиска выбросов:
1. Коробчатые диаграммы — на изображении ниже вы можете видеть, что несколько точек существуют вне коробки. Коробка — это центральная тенденция данных. Он сгруппирован вокруг среднего значения. Линия верхней границы — это предел централизации этих данных.
2. Квартили — представляют, как данные разбиваются на кварталы.
Лучшие онлайн-курсы по Excel!
Что такое квартили и как их использовать
Вы можете легко идентифицировать выбросы в ваших данных с помощью диаграмм Box и Whisker. Это графики, которые показывают, как данные группируются вокруг центральной меры, такой как медианное (среднее) значение в данных. Квартиль — это точка разделения, которая разделяет данные на кварталы. Есть 3 квартиля: Q1, Q2 и Q3.Первый квартиль (Q1) — это нижняя четверть ваших данных, в которой находятся меньшие значения. Q2 — это среднее значение, а Q3 — это верхняя часть данных. Диапазон значений от Q1 до Q3 называется межквартильным диапазоном (IQR).
Выбросы находятся за пределами диапазона внутреннего квартиля. По статистике, они живут в 1,5 раза ниже или выше вашего внутреннего квартильного диапазона.
Графики в виде прямоугольников и усов показывают, где находятся данные в пределах вашего внутреннего квартильного диапазона и за его пределами
Вот формула, которую вы будете использовать в Excel.
КВАРТИЛЬ (массив, кварт)
Два аргумента в функциях — это данные, которые вы хотите исследовать. Другими словами, когда у вас есть данные в Excel, это в основном список значений в столбце. Этот список — ваш массив данных. Другая часть аргументов — это квартиль, который вы хотите определить. Например, 1, 2, 3.
Вы должны определить 1-й и 3-й квартили с помощью этой формулы
Как рассчитать диапазон внутреннего квартиля.Он рассчитывается путем вычитания Q1 из Q3. Его можно назвать IQR
. Далее вам нужно будет вычислить верхний и нижний предел вашего диапазона или ваших данных.