Youtubezilla.ru

Мастер бытовой техники
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

4 способа поиска данных в таблице Excel

Exceltip

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

Функция ВПР()

Формула ВПР

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

формула ВПР не работает

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

формула ИНДЕКС

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

Описание формулы ПОИСКПОЗ

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

формула индекс и поискпоз

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

Читайте так же:
Простые приёмы установки и удаления приложений в Стим

Найти

Комбинация клавиш Ctrl+F позволяет быстро вызвать окно функции Найти. Также можно активировать инструмент через отдельную кнопку на Панели инструментов.

Поиск в экселе 5

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

Поиск в экселе 6

Данная функция реализована и в виде формулы с одноименным названием, которая также позволяет осуществлять поиск по словам. Рассмотрим работу на примере списка фруктов. Вписываете формулу =НАЙТИ(«а»;$B$4:$B$11), применяете автозаполнение и получаете результат:

Поиск в экселе 7

Кросс-таблица — это сводная таблица, в которой заголовки могут располагаться как по строкам, так и по столбцам, позволяя таким образом отразить взаимосвязь двух и более переменных друг с другом.

Для настройки структуры кросс-таблицы для выбранного источника импорта:

Нажмите на кнопку « Настройки » на странице «Настройка импорта».

В появившемся диалоговом окне установите флажок « Кросс-таблица ».

Перейдите на появившуюся в мастере создания и редактирования источника данных «Excel» страницу « Структура кросс-таблицы »:

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

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

ВПР – инструкция для работы с двумя условиями

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

Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

Характеристики телефонов таблица 4

НазваниеОЗУКамера
ZTE0,55
ZTE15
DNS18
DNS0,58
Alcatel18
Alcatel2562

Характеристики телефонов таблица 5

НазваниеОЗУЦена
ZTE0,51 990 ₽
ZTE13 099 ₽
DNS13 100 ₽
DNS0,52 240 ₽
Alcatel14 500 ₽
Alcatel256450 ₽

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

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

ОбъединениеНазваниеОЗУЦена
ZTE 0,5ZTE0,51 990 ₽
ZTE 1ZTE13 099 ₽
DNS1DNS13 100 ₽
DNS 0,5DNS0,52 240 ₽
Alcatel 1Alcatel14 500 ₽
Alcatel 256Alcatel256450 ₽
Читайте так же:
Как замедлить видео

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

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

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL ДЛЯ ЧАЙНИКОВ

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

Посмотрите внимательно на рабочий лист табличного процессора:

Электронная таблица.

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Выделить столбец.

Для выделения строки – по названию строки (по цифре).

Выделить строку.

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

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

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Ширина столбцов.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Автоподбор высоты строки.

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

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

Место для вставки столбца.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Добавить ячейки.

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Читайте так же:
Включение Авито Доставки: особенности и порядок действий

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

Границы таблицы.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Меню шрифт.

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

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

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

  1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть. * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
  2. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
  3. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

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

Также вы можете зайти в настройки:

  1. В окне «Найти» нажмите «Параметры».
  2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
  3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
  4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

  1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
  2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
  3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск по формату

Поиск нескольких слов

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

Читайте так же:
Переустановка материнской платы без переустановки Windows 7

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

  1. Напишите их в строке поиска.
  2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
  3. Этим способом можно задать ключ даже с отдельными буквами.

Найти внешние ссылки в Excel

Внешняя ссылка — это ссылка или ссылка на ячейку или диапазон на листе в другой книге Excel или ссылка на определенное имя в другой книге.

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

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

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

Преимущества использования внешних ссылок или внешних ссылок в Excel:

  1. Мы можем объединить данные из многих рабочих книг.
  2. Мы можем работать над одним листом с множеством ссылок из других листов, не открывая их.
  3. Мы можем лучше рассмотреть наши данные. Вместо того, чтобы иметь большую сумму данных в одной рабочей таблице, мы можем использовать нашу панель инструментов или отчет в одной рабочей таблице.

Как создать внешние ссылки в Excel?

Давайте создадим внешнюю ссылку в книге с примером,

Предположим, у нас есть пять человек, которые должны проверить 100 вопросов, и они должны пометить свои ответы как правильные или неправильные. У нас есть три разные рабочие тетради. В рабочей книге 1 мы должны собрать все данные, которые названы как отчеты, тогда как в рабочей книге 2, которая названа правильной, которая содержит данные, помеченные ими как правильные, и в рабочей книге 3, которая названа как неправильные, есть данные неправильных значений.

Взгляните на рабочую книгу 1 или рабочую книгу отчета:

На приведенном выше изображении в столбце A указаны имена людей, а в столбце B — общее количество вопросов. В столбце C он будет содержать количество правильных ответов, а в столбце D — количество неправильных ответов.

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

  • Выход будет таким, как указано ниже.

  • Перетащите формулу в оставшиеся ячейки.
  • Теперь в ячейке D2 используйте функцию VLOOKUP из рабочей книги 3, т.е. неверную рабочую книгу, чтобы получить значение неправильных ответов, помеченных Анандом.

  • Выход будет таким, как указано ниже.

  • Перетащите формулу в оставшиеся ячейки.

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

Читайте так же:
TOP-10 приложений погоды на Android

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

Как найти внешние ссылки в Excel?

Есть два разных способа найти внешнюю ссылку или ссылки в книге Excel. Давайте разберемся, как найти внешние ссылки на нескольких примерах.

Найти внешние ссылки в Excel — Пример № 1

У нас есть книга «Отчет», и нам нужно найти внешние ссылки в этой книге Excel.

  • Нажмите Ctrl + F, и появится диалоговое окно «Найти и заменить».

  • Нажмите « Параметры» в правой нижней части диалогового окна.

  • В поле « Найти» введите «* .xl *» (расширение других рабочих книг или внешних ссылок — * .xl * или * .xlsx).

  • В поле «Внутри» выберите « Рабочая книга» .

  • И в поле «Посмотри в поле» выберите « Формулы» .

  • Нажмите на Найти все .

  • Он отображает все внешние ссылки в этой книге.

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

Найти внешние ссылки в Excel — Пример № 2

Вторая процедура из опции Изменить ссылки .

  • На вкладке « Данные » есть раздел соединений, где мы можем найти опцию «Редактировать ссылки». Нажмите на ссылку Изменить.

  • Показывает внешние ссылки в текущей книге.

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

Примечание. По умолчанию этот параметр остается отключенным и активируется только в том случае, если наша рабочая книга имеет внешние ссылки на него.

Объяснение внешних ссылок в Excel

Как объяснялось ранее, зачем нам внешние ссылки на наш лист? Быстрый ответ будет: мы не можем хранить большие объемы данных в одной книге. В этом сценарии нам нужно хранить данные в разных таблицах и ссылаться на значения в основной книге.

Теперь, почему нам нужно найти внешние ссылки в книге?

Иногда нам нужно обновить или удалить наши ссылки, чтобы изменить или обновить значения. В таком случае нам сначала нужно найти внешние ссылки.

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

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

То, что нужно запомнить

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

  • Нажмите «Включить содержимое», затем продолжите.

Рекомендуемые статьи

Это было руководство по поиску внешних ссылок в Excel. Здесь мы обсудим, как найти внешние ссылки в Excel вместе с примерами Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector