Учебное пособие по Microsoft Excel: найдите адрес совпадающих ячеек.
Добро пожаловать в еще один захватывающий эпизод Dueling Excel, где мы решаем сложные задачи Excel и находим лучшие решения. В этом выпуске мы решаем вопрос поиска адреса совпадающей ячейки в матрице. Наш зритель, Карлос из Португалии, хочет найти точное совпадение числового значения и получить ссылку на ячейку. Как всегда, я обращаюсь к VBA за пользовательской функцией, а мой соведущий Майк использует формульный подход.
В VBA я пишу функцию под названием «где есть», которая принимает два аргумента: значение для поиска и диапазон для поиска. Используя цикл, я проверяю каждую ячейку в диапазоне, и если совпадение найдено, функция возвращает адрес ячейки. Если совпадение не найдено, функция возвращает ошибку «Н/Д». Майк, с другой стороны, использует комбинацию функций ADDRESS, ROW и COLUMN для достижения того же результата. Он также представляет функцию INDEX для поиска ссылки на ячейку, избегая использования изменчивой функции INDIRECT.
Но настоящее волшебство происходит, когда Майк объединяет функции СУММПРОИЗВ и СТРОКА, чтобы получить номер строки, и функцию СТОЛБЕЦ, чтобы получить номер столбца. Затем он использует эти значения в функции ИНДЕКС для возврата ссылки на ячейку. И вот что интересно: он добавляет малоизвестный прием использования функции SUM для преобразования ссылки на ячейку в динамический диапазон. Это означает, что формула будет автоматически корректироваться при изменении размера матрицы. Совершенно потрясающе!
Завершая этот эпизод, я не могу не упомянуть, как улучшились навыки Майка по формулам с тех пор, как он начал работать над вводом контрольной смены. Его решения стали более элегантными и эффективными, что дает VBA возможность конкурировать за свои деньги. Говоря о деньгах, не забудьте взять с собой копию нашей книги «Ctrl+Shift+Enter: освоение формул массивов Excel», где вы найдете еще больше замечательных советов и приемов. Благодарим вас за внимание. Увидимся на следующей неделе для просмотра еще одного захватывающего эпизода Dueling Excel.
Купите последнюю книгу Билла Джелена по Excel: https://www.mrexcel.com/products/latest/
Вы можете помочь моему каналу, нажав «Нравится» или оставив комментарий ниже: https://www.mrexcel.com/like-mrexcel-...
Оглавление:
(00:00) Адрес соответствующей ячейки
(00:13) Поиск точного совпадения числового значения
(00:23) Использование VBA для написания пользовательской функции
(00:33) Создание функции «Где находится»
(01:11) Выход из цикла и инициализация функции
(01:21) Тестирование функции
(02:06) Использование функции адреса
(02:16) Использование индексной функции
(02:30) Умножение и выделение матрицы
(03:00) Использование функции строки
(03:42) Использование функции столбца
(04:06) Использование адреса и косвенных функций
(04:32) Создание динамического диапазона
(06:00) Использование индексной функции для двустороннего поиска
(07:44) Нажатие «Нравится» очень помогает алгоритму.
Присоединяйтесь к обсуждению этого видео на доске объявлений MrExcel по адресу https://www.mrexcel.com/board/threads....
Майк «ExcelisFun» Гирвин и Билл «MrExcel» Джелен снова показывают нам множество методов достижения намеченного результата. Сегодня в выпуске № 1641 подкаст Dueling Excel посвящен поиску адресов совпадающих ячеек. Карлос хочет найти точное соответствие значению в своей матрице данных и вернуть адрес ячейки, содержащей это значение. Майк и Билл по-разному подходят к вопросу и средствам достижения результата. Следуйте дальше, чтобы увидеть, как это делается!
Подкаст «Дуэль Excel» № 110... Этот эпизод является дополнением к видеоподкасту к книге Майка Гирвина и Билла Джелена «Убийство драконов Excel: руководство для начинающих, как преодолеть разочарования в Excel и сделать Excel интересным». [В настоящее время доступно в виде электронной книги/печатного издания и в виде комплекта DVD] http://www.mrexcel.com/slayingdragons...
и
«Изучите Excel 2007–2010 от MrExcel». Скачивайте новое двухминутное видео каждый рабочий день, чтобы узнать одну из 512 разгаданных тайн Excel! и на 35% больше советов, чем в предыдущем издании книги Билла! http://www.mrexcel.com/learn2010/LE20...
Информация по комментариям в разработке