Подсчет количества уникальных значений среди повторяющихся. Поиск в столбце в excel

Поиск и выделение ячеек, соответствующих определенным условиям

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

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

Сделайте следующее:

Начинать, выполнив одно из указанных ниже действий.
- Чтобы выполнить поиск определенных ячеек на всем листе, щелкните любую ячейку.
- Чтобы выполнить поиск определенных ячеек в пределах области, определенной, выберите диапазон, строк или столбцов, которые должны. Дополнительные сведения читайте в статье Выбор ячеек, диапазонов, строк или столбцов на листе.
  
  Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
На вкладке " Главная " нажмите кнопку Найти и выделить > Перейти (в группе " Редактирование ").

Сочетание клавиш: Нажмите клавиши CTRL + G.
Нажмите кнопку Дополнительный.
В диалоговом окне Выделить группу ячеек выберите один из указанных ниже вариантов.

Щелкните

Чтобы выделить

Примечания	Ячейки с примечаниями.
Константы	Ячейки, содержащие константы.
формулы	Ячейки, содержащие формулы. Примечание: Флажки под параметром формулы определяют тип формул.
Пустые	Пустые ячейки.
Текущую область	текущая область, например весь список.
Текущий массив	Весь массив, если активная ячейка содержится в массиве.
Объекты	Графические объекты (в том числе диаграммы и кнопки) на листе и в текстовых полях.
Отличия по строкам	Все ячейки, которые отличаются от активной ячейки в выбранной строке. В режиме выбора всегда найдется одной активной ячейки, является ли диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, вы можете изменить расположение активной ячейки, которые по умолчанию — первую ячейку в строке. Если выбран более одной строки, сравнение выполняется для каждого отдельного ряда выделенного фрагмента и ячейку, которая используется для сравнения для каждой дополнительной строки находится в том же столбце как активную ячейку.
Отличия по столбцам	Все ячейки, которые отличаются от активной ячейки в выделенном столбце. Всегда найдется одной активной ячейки в выделенном фрагменте, будет ли это диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, можно изменить расположение на активную ячейку — по умолчанию — первую ячейку в столбце. Если выбрать более одного столбца, сравнение выполняется для каждого отдельного столбца выделенного фрагмента. Ячейки, которая используется для сравнения для каждого дополнительного столбца находится в той же строке в активной ячейке.
Влияющие ячейки	Ячейки, на которые ссылается формула в активной ячейке. В разделе зависимые ячейки выполните одно из указанных ниже действий. только непосредственно, чтобы найти только те ячейки, на которые формулы ссылаются непосредственно; на всех уровнях, чтобы найти все ячейки, на которые ячейки в выделенном диапазоне ссылаются непосредственно или косвенно.
Зависимые ячейки	Ячейки с формулами, которые ссылаются на активную ячейку. Выполните одно из указанных ниже действий. Выберите вариант только непосредственно, чтобы найти только ячейки с формулами, ссылающимися непосредственно на активную ячейку. Выберите вариант на всех уровнях, чтобы найти все ячейки, ссылающиеся на активную ячейку непосредственно или косвенно.
Последнюю ячейку	Последнюю ячейку на листе, содержащую данные или форматирование.
Только видимые ячейки	Только ячейки, видимые в диапазоне, который пересекается со скрытыми строками и столбцами.
Условные форматы	Только ячейки, которые применено условное форматирование. В разделе "Проверка данных" выполните одно из указанных ниже действий. все, чтобы найти все ячейки, к которым применено условное форматирование; этих же, чтобы найти ячейки с тем же условным форматированием, что и в выделенной ячейке.
Проверка данных	Только ячейки, которые применены правила проверки данных. Выполните одно из указанных ниже действий. Выберите вариант все, чтобы найти все ячейки, для которых включена проверка данных. Выберите вариант этих же, чтобы найти ячейки, к которым применены те же правила проверки данных, что и к выделенной ячейке.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

support.office.com

Как найти столбец в «Excel» или поиск потеряного столбца

Пару дней назад, ко мне подошёл коллега с вопросом: » У меня в таблице «Excel» пропал столбец. Как его найти?»

На вопрос не удалил ли он столбец коллега ответил: «Нет, я не удалял, столбец есть в формулах, но его не видно.» Исчезнувший столбец в Эксель

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

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

Способ первый — «Найти столбец №1»

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

Второй способ — «Найти столбец №2″(подходит для людей с острым зрением).

Навести курсор на границу столбцов между которыми находится потерянный столбец.
Когда курсор превратится в две вертикальные параллельные прямые. (как значок конденсатора в схемах в радиотехнике), кликнуть двойным кликом.
Поиск потеряного столбца №2
Скрытый столбец появится.

Надеюсь приведенная информация будет полезной для Вас и облегчит работу с программой «Excel».

ruexcel.ru

Скрытие и отображение строк и столбцов

Строку или столбец можно скрыть, воспользовавшись командой Скрыть или изменив высоту строки или ширину столбца до нулевого значения (0). Чтобы отобразить их снова, можно воспользоваться командой Отобразить.

Можно отобразить отдельные строки или столбцы или все скрытые строки и столбцы сразу. Чтобы скрыть первую строку или столбец на листе, необходимо выполнить другую процедуру. Она описана в последнем разделе статьи.

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

В этой статье

Скрытие одной или нескольких строк или столбцов

Отображение одной или нескольких скрытых строк или столбцов

Одновременное отображение всех скрытых строк и столбцов

Отображение первой строки или столбца

Скрытие одной или нескольких строк или столбцов

Выберите строки или столбцы, которые нужно скрыть.

Выделение ячеек, диапазонов, строк и столбцов
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выполните одно из следующих действий.
- В группе Видимость выделите пункт Скрыть или отобразить, а затем выберите команду Скрыть строки или Скрыть столбцы.
- В группе Размер ячейки выберите пункт Высота строки или Ширина столбца, а затем введите 0 в поле Высота строки или Ширина столбца.

Совет: Можно щелкнуть правой кнопкой строку или столбец (либо несколько выделенных строк или столбцов) и выбрать команду Скрыть.

К началу страницы

Отображение одной или нескольких скрытых строк или столбцов

Выполните одно из следующих действий.
- Чтобы отобразить скрытые строки, выделите строку выше и строку ниже строк, которые нужно отобразить.
- Чтобы отобразить скрытые столбцы, выделите столбцы, прилегающие с обеих сторон к столбцам, которые нужно отобразить.
- Чтобы отобразить первую строку или первый столбец листа, выберите их, введя значение A1 в поле Имя рядом со строка формул.
  
  Совет: Эту ячейку можно выделить также с помощью диалогового окна Перейти. На вкладке Главная в группе Редактирование нажмите кнопку Найти и заменить и выберите команду Перейти. В поле Ссылка введите значение A1 и нажмите кнопку ОК.
  
  Выделение ячеек, диапазонов, строк и столбцов
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выполните одно из следующих действий.
- В группе Видимость выделите пункт Скрыть или отобразить, а затем выберите пункт Отобразить строки или Отобразить столбцы.
- В группе Размер ячейки щелкните пункт Высота строки или Ширина столбца, а затем введите нужное значение в поле Высота строки или Ширина столбца.

Совет: Можно также щелкнуть правой кнопкой мыши выделенный диапазон видимых строк и столбцов, окружающих скрытые строки и столбцы, и выбрать пункт Отобразить.

К началу страницы

Одновременное отображение всех скрытых строк и столбцов

Для выбора всех ячеек листа выполните одно из следующих действий:
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выполните одно из следующих действий.
- В группе Видимость выделите пункт Скрыть или отобразить, а затем выберите пункт Отобразить строки или Отобразить столбцы.
- В группе Размер ячейки щелкните пункт Высота строки или Ширина столбца, а затем введите нужное значение в поле Высота строки или Ширина столбца. Высота строк по умолчанию составляет 15, а ширина столбцов — 8,43.

К началу страницы

Отображение первой строки или столбца

Выполните указанные ниже шаги, если была скрыта первая строка или столбец на листе.

Чтобы выделить скрытую первую строку или скрытый первый столбец листа, выполните одно из описанных ниже действий.
- В поле Имя рядом со строкой формул введите значение A1.
- На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и выберите команду Перейти. В поле Ссылка введите значение A1 и нажмите кнопку ОК.
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выполните одно из следующих действий.
- В группе Видимость выделите пункт Скрыть или отобразить, а затем выберите пункт Отобразить строки или Отобразить столбцы.
- В группе Размер ячейки щелкните пункт Высота строки или Ширина столбца, а затем введите нужное значение в поле Высота строки или Ширина столбца.

К началу страницы

Дополнительные сведения

См. также

Скрытие и отображение листов и книг

Вставка и удаление листов

support.office.com

Подсчет количества уникальных значений среди повторяющихся

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

числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
строки "Руслан", "Сергей", "Сергей", "Сергей", будут найдены два уникальных значения — "Руслан" и "Сергей".

Существует несколько способов подсчета количества уникальных значений среди повторяющихся.

В этой статье

Подсчет количества уникальных значений с помощью фильтра

Подсчет количества уникальных значений с помощью функций

Подсчет количества уникальных значений с помощью фильтра

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

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

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

Появится диалоговое окно Расширенный фильтр.
Установите переключатель скопировать результат в другое место.
В поле Копировать введите ссылку на ячейку.

Кроме того нажмите кнопку Свернуть диалоговое окно временно скрыть диалоговое окно, выберите ячейку на листе и затем нажмите кнопку Развернуть диалоговое окно .
Установите флажок Только уникальные записи и нажмите кнопку ОК.

Уникальные значения из выделенного диапазона будут скопированы в новое место, начиная с ячейки, указанной в поле Копировать.
В пустую ячейку под последней ячейки в диапазоне введите функцию СТРОК . Используйте диапазон уникальных значений, которые были скопированы в качестве аргумента, за исключением заголовок столбца. Например если диапазон уникальных значений B2:B45, введите =ROWS(B2:B45).

К началу страницы

Подсчет количества уникальных значений с помощью функций

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
Вычислите сумму, используя функцию СУММ.
Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
Узнайте номер позиции текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

Пример

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

Копирование примера

Создайте пустую книгу или лист.
Выделите пример в разделе справки.

Примечание: Не выделяйте заголовки строк или столбцов.

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

A	B
Данные	Данные
986	Руслан
Дмитрий	563
67	789
	235
Руслан	Дмитрий
689	789
Дмитрий	143
56	237
67	235
Формула	Описание (результат)
=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1))	Подсчет количества уникальных числовых значений в диапазоне A2:A10 без учета пустых ячеек и текстовых значений (4)
=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))	Подсчет количества уникальных текстовых и числовых значений в диапазоне B2:B10, который не должен содержать пустые ячейки (7)
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""))>0,1))	Подсчет количества уникальных текстовых и числовых значений в диапазоне A2:A10 без учета пустых ячеек и текстовых значений (6)

Примечания

Формулы в этом примере должны быть введены как формулы массива. Выделите все ячейки, содержащие формулу, нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ВВОД.
Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вычисление формулы.

Описание функций

Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25,A1:A3,0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.
Функция ДЛСТР возвращает число символов в текстовой строке.
Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

К началу страницы

support.office.com

Разбиение текста на столбцы с помощью функций

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

Скопируйте ячейки из таблицы и вставьте на лист Excel, начиная с ячейки A1. Формула слева будет отображаться для справки, а формулу справа Excel автоматически преобразует в соответствующий результат.

Совет. Прежде чем вставлять данные на лист, установите для столбцов A и B ширину в 250 пикселей.

Имя

Имя начинается с двенадцатого знака (Д) и заканчивается пятнадцатым знаком (третий пробел). Формула извлекает три знака, начиная с двенадцатой позиции.

Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

Найдите позицию первого пробела в ячейке A2, начиная слева (6).

Для получения позиции знака после первого пробела (м) добавьте 1. Результат будет являться позицией, с которой начинается поиск второго пробела (6 + 1 = 7).

Найдите второй пробел в ячейке A2, начиная с седьмой позиции (м), найденной на шаге 2 (11).

Для получения знака после второго пробела (Д) добавьте 1. Результатом будет начальная позиция для имени (11 + 1 = 12).

Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК:

Найдите позицию знака после второго пробела (Д). Результатом будет позиция, с которой начинается поиск третьего пробела (12).

Найдите позицию третьего пробела в A2, начиная слева. Результатом будет конечная позиция имени (15).

Найдите позицию знака после второго пробела (Д). Результатом будет начальная позиция имени (12).

Возьмите численное значение третьего пробела, найденное на шаге 6, и вычтите значение (Д), найденное на шаге 7. Результатом будет число знаков, которое функция ПСТР извлекает из текстовой строки, начиная с двенадцатой позиции, найденной на шаге 4 (15 - 12 = 3).

Отчество

Второе имя начинается со второго знака, начиная справа (К). Формула извлекает два знака справа.

Найдите позицию первого пробела в ячейке A2, начиная слева (6).

Найдите второй пробел в ячейке A2, начиная с седьмой позиции (м), найденной на шаге 2 (11).

Найдите позицию третьего пробела в A2, начиная слева. Результатом будет конечная позиция второго имени (15).

Подсчитайте общую длину текстовой строки в A2, а затем вычтите количество знаков слева до третьего пробела, найденное на шаге 5. Результатом будет количество знаков, которое необходимо извлечь справа от полного имени (17 - 15 = 2).

Фамилия

Фамилия начинается с первого знака слева (Б) и заканчивается шестым знаком (первый пробел). Поэтому формула извлекает шесть знаков слева.

Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК:

Найдите позицию первого пробела в ячейке A2, начиная слева (6).

Суффикс

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

Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

Найдите позицию первого пробела в ячейке A2, начиная слева (6).

Для получения позиции знака после первого пробела (м) добавьте 1. Эта позиция будет начальной позицией для суффикса (6 + 1 = 7).

Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК:

Найдите позицию первого пробела в ячейке A2, начиная слева (6).

Для получения позиции знака после первого пробела (м) добавьте 1. Результатом будет позиция, с которой начинается поиск второго пробела (7).

Найдите позицию второго пробела в ячейке A2, начиная с седьмого знака, найденного на шаге 4 (11).

Для получения позиции знака запятой (,) вычтите 1 из численного значения второго пробела, найденного на шаге 4. Результатом будет конечная позиция суффикса (11 - 1 = 10).

Найдите позицию первого пробела (6).

После нахождения первого пробела добавьте 1 для поиска позиции следующего символа (м), которая также находилась в пунктах 3 и 4 (7).

Возьмите численное значение знака запятой (,), найденное на шаге 6, и вычтите численное значение знака (м), найденное на шагах 3 и 4. Результатом будет количество знаков, которое функция ПСТР извлекает из текстовой строки, начиная с седьмой позиции, найденное на шаге 2 (10 - 7 = 3).

support.office.com

Поиск наименьшего или наибольшего числа в диапазоне

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

Для ячеек, расположенных подряд в одной строке или одном столбце

Выделите ячейку, находящуюся под числами, наименьшее из которых требуется найти, или справа от них.
На вкладке Главная в группе Редактирование нажмите стрелку рядом с кнопкой Сумма и выберите команду Минимум (поиск наименьшего) или Максимум (поиск наибольшего), а затем нажмите клавишу ВВОД.

Для ячеек, расположенных вразброс

Для выполнения этой задачи используются функции МИН, МАКС, НАИМЕНЬШИЙ или НАИБОЛЬШИЙ.

Пример

Скопируйте указанные ниже данные на пустой лист.

A
Данные
10
7
9
27
0
4
Формула	Описание (результат)
=МИН(A2:A7)	Поиск наименьшего числа в диапазоне (0)
=МАКС(A2:A7)	Поиск наибольшего числа в диапазоне (27)
=НАИМЕНЬШИЙ(A2:A7;2)	Поиск второго наименьшего числа в диапазоне (4)
=НАИБОЛЬШИЙ(A2:A7;3)	Поиск третьего наибольшего числа в диапазоне (9)

Дополнительные сведения

См. также

НАИБОЛЬШИЙ

МАКС

МИН

НАИМЕНЬШИЙ

support.office.com

Отображение первого столбца или строки на листе

Если первая строка (строку 1) или столбец (столбец A) не отображается на листе, немного сложнее отобразить его, так как нет простой способ выделите строки или столбца. Можно выбрать весь лист, а затем отобразить строки или столбцы (вкладка "Главная ", группа " ячейки ", кнопка " Формат ", команда Скрыть или отобразить ), но отображение всех скрытых строк и столбцов на листе, вы не можете Выполните действие. Вместо этого можно использовать поле " имя " или команды Перейти выберите первая строка и столбец.

Чтобы выделить скрытую первую строку или скрытый первый столбец листа, выполните одно из описанных ниже действий.
- В Поле имя рядом с строка формул введите значение A1и нажмите клавишу ВВОД.
- На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и выберите команду Перейти. В поле Ссылка введите значение A1 и нажмите кнопку ОК.
На вкладке " Главная " в группе ячейки нажмите кнопку Формат.
Выполните одно из указанных ниже действий.
- В группе Видимость щелкните пункт Скрыть или отобразить, а затем выберите команду Отобразить строки или Отобразить столбцы.
- В группе Размер ячейки выберите пункт Высота строки или Ширина столбца, а затем введите нужное значение высоты строки или ширины столбца в соответствующие поля Высота строки и Ширина столбца.
  
  Совет: По умолчанию высота строки имеет значение 15, а по умолчанию ширина столбца — 8,43.

support.office.com