Адрес клітинки за ознакою пошуку в MS Excel

Формула MS Excel  для нахождения адреса ячейки с максимальным значением в двумерном диапазоне

Как можно найти и указать адрес ячейки со значением 1 в массиве =B2:D12?

1 способ.  =АДРЕС(СУММПРОИЗВ((B2:D12=1)*СТРОКА(B2:D12));СУММПРОИЗВ((B2:D12=1)*СТОЛБЕЦ(B2:D12)))

2 способ.
=АДРЕС(СУММПРОИЗВ((B2:D12=1)*СТРОКА(B2:D12));СУММПРОИЗВ((B2:D12=1)*СТОЛБЕЦ(B2:D12)))

 формулы SUMPRODUCT работают, но иногда виснет Эксель. 
Это из-за того, что я прописал большой диапазон ? Массив $a$1:$aa$65000

Третий способ

Формулы массива с каждым днем нравятся мне всё больше и больше!  Недавно у меня возникла задача – найти адрес ячейки, содержащей максимальное значение в диапазоне. Для начала я создал тестовый диапазон А1:F10 (рис. 1), заполнив его случайными целыми числами от 1 до 100 с помощью функции =СЛУЧМЕЖДУ(1;100).

Рис. 1. Исходный диапазон

Скачать заметку в формате Word, примеры в формате Excel

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

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

Рис. 2. Условное форматирование по всем ячейкам диапазона позволяет выделять цветом ячейки, содержащие максимальное и минимальное значения

Напомню, что функция =СЛУЧМЕЖДУ() обновляется всякий раз, когда в Excel выполняется какая-либо операция с числами (но не форматирование). Можно просто жать F9 (эквивалентно команде «пересчитать»).

Кто не знаком с формулами массива, рекомендую начать с прочтения заметки Введение в формулы массива.

Аналог формулы для вычисления адреса ячейки с максимальным значением в диапазоне я нашел в книге Уокенбаха «Формулы в MS Excel 2010»:

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

Для того, чтобы воспользоваться формулой Уокенбаха, надо присвоить имя нашему диапазону А1:F10. Я создал динамический массив под именем массив (рис. 3). Для упрощения можно создать статический массив, набрав вместо формулы, выделенной на рис. 3, следующее: =Лист1!$A$1:$F$10. Преимущество динамического массива на основе функции СМЕЩ проявятся позже, если вам понадобится расширить рамки вашего диапазона. Например, добавить строки и/или столбцы. Если при этом вы создали статический диапазон, вам понадобится руками изменить область определения массива под именем массив. Если же у вас был создан динамический массив, никаких изменений вносить не потребуется! Кто не знаком с работой функции СМЕЩ, рекомендую почитатьАвтоматическое обновление сводной таблицы.

Рис. 3. Присвоение имени двумерному диапазону

Рассмотрим как работает формула Уокенбаха:

Функция ЕСЛИ создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру строки этой ячейки, в противном случае элемент массива равен пустой строке. Функция МИН использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер строки, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер первой строки, где содержится это значение.

К сожалению, формула Уокенбаха предназначена для одномерного вертикального диапазона, поэтому она всегда возвращает адрес ячейки из столбца А, например, $A$8, когда правильное значение было бы $D$8.

Чтобы приспособить  формулу Уокенбаха для вычисления адреса ячейки с максимальным значением в двумерном диапазоне, создадим еще один динамический массив – столбМакс (рис. 4)

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

Рассмотрим подробнее, как функция СМЕЩ формирует этот одномерный вертикальный диапазон (рис. 5). Кстати, здесь, в отличие от выше описанного случая, не обойтись без динамического массива, так как заранее номер столбца, содержащего ячейку с максимальным значением, не известен…

Рис. 5. Функция СМЕЩ, динамически формирующая одномерный вертикальный диапазон

Функция ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»») создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру столбца этой ячейки, в противном случае элемент массива равен пустой строке.

Функция МИН(ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»»)) использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер столбца, где содержится максимальное значение диапазона массив. Если в диапазонемассив имеется несколько ячеек с максимальными значениями, то возвращается номер самого левого столбца, где содержится это максимальное значение.

Итоговая формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне имеет следующий вид (рис. 6):

Рис. 6. Формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне

Подробно изучить, как работает формула можно с помощью сервиса Excel – пошаговое вычисление формулы (рис. 7).

Рис. 7. Пошаговое вычисление формулы

Аналогично создается и формула для нахождения адреса ячейки с минимальнымзначением в двумерном диапазоне:

1. Створення макросів 

Макрос – це набір інструкцій, які задають набір послідовних дій, які Microsoft виконує замість 
вас. 
 Макроси по своєму запису – це комп’ютерні програми, які виконуються і працюють 
тільки в середовищі Excel. 
Макроси призначені для швидкого виконання послідовності дій. 
Макроси бувають простими і складними. 
Вони можуть працювати в інтерактивному ритмі. 
 Існує два способи макросів: 
1. автоматичний запис послідовності своїх дій; 
2. інструкції вводяться в ручну на окремому листі Excel, який називається модулем. 
Процес запису макросу складається з трьох кроків: 
- активізується режим запису макроса; 
- присвоюється йому ім’я; 
- виконуються дії, які потрібно записати. 

При записі макросу Excel записує всі дії і вставляє в модуль відповідні інструкції 
Visual Basic. Для того, щоб почати новий модуль виберіть в меню Сервіс/Макрос/Макрос. 
Після чого в діалоговому вікні макрос виділіть ім’я макроса, який був створений і натисніть 
кнопку „изменить” і з’явиться модуль дій, який містить вибраний макрос. 

Запуск макроса здійснюється комбінацією клавіш, які призначено йому в діалоговому 
вікні запис макросу. Такою комбінацією може бути будь-яке сполучення кнопок, тобто Ctrl і 
прописна буква. 

Макроси можуть застосовуватись для виконання таких дій : 

• відкривання і закривання  таблиць, запитів, форм, звітів; 
• фільтрування, пошук та перехід до певного запису БД; 
• виведення на екран інформаційних повідомлень; 
• подачу звукового сигналу; запуск та вихід з  додатків та ін. 

Найчастіше для виконання дій, що часто повторюються, і використовують 
макроси. 

2. Елементи керування 

Елемент керування – об'єкт графічного інтерфейсу користувача (такий як поле, 
прапорець, смуга прокручування або кнопка), що дозволяє користувачам керувати додатком.  19
Елементи керування використовують для відображення даних або параметрів, для виконання 
дій, або для спрощення роботи з інтерфейсом користувача. 
В Microsoft Excel існує два види елементів керування. Елементи керування ActiveX, 
які підходять в більшості випадків і працюють з макросами Visual Basic для додатків (VBA) 
та з WEB-сценаріями. Елементи керування з панелі Формы, які сумісні з ранніми версіями 
Microsoft Excel, починаючи з версії 5.0, та можуть використовуватися на листах макросів 
XLM. 
Елементи ActіveX використовуються для більшості інтерактивних форм, особливо у 
випадку, якщо потрібно управляти виникненням різних подій при використанні елемента 
керування. Наприклад, на аркуш можна додати вікно списку, а потім написати макрос, що 
виконує різні дії залежно від того, яке значення обране користувачем зі списку. Елементи 
керування ActіveX аналогічні елементам керування мов програмування, таких як Mіcrosoft 
Vіsual Basіc, використання даних елементів вимагає знання цієї мови програмування для 
написання макросу. 
Елементи керування панелі інструментів Формы варто використати, якщо потрібно 
записати всі макроси для форми без написання або зміни коду макросів в VBA. Дані 
елементи керування призначені для використання у формах на аркушах, заповнюваних 
користувачами за допомогою Mіcrosoft Excel. Елементу керування можна призначити 
наявний макрос або написати чи записати новий макрос. Коли користувач форми клацає на 
елемент керування, відбувається запуск макросу. 
Елементи керування панелі інструментів Формы перебувають на панелі інструментів 
Формы, яку можна викликати скориставшись командою ВидПанели 
инструментовФормы. 
Щоб задати властивості для наявного елемента керування, необхідно клацнути на 
ньому правою кнопкою миші, та вибрати з контекстного меню команду Формат об"єкта, а 
потім відкрити вкладку Элемент управления. Кнопки й написи не мають властивостей. 
Зв’язок елемента з коміркою використовується для отримання логічного чи 
порядкового значення елемента для використання його при певних обчисленнях.

3. VBA основні положення 

Відкрити Сервіс/Макрос/Редактор Visual Basic, або Alt+F11 
Закрити Alt+Q 
VBA- Visual Basic for Applications –це програмний додаток створений спеціально для 
роботи з додатками Microsoft Office. 
Цей програмний додаток розроблений для створення макросів в інших додатках. 
Макрос – це набір інструкцій, які повідомляють програмі які дії потрібно виконати що 
досягнути визначеної мети. 
Макрос об’єднує інструкції в один сценарій. Список інструкцій складається з 
макрооператорів. При записі макросів створюється процедура. 
Процедурою називається набір зв’язаних операторів, які утворюють один блок і 
виконують певну задачу. 
Процедури поділяють на дві групи: Командні макроси (або просто макроси) і 
визначені користувачем Користувацькі функції (або просто функції) 
Командні макроси найбільш поширені вони містять оператори які рівнозначні 
командам меню чи іншим командам командний макрос може впливати на поточний 
документ. 
Макрос створюють двома способами ручним і автоматизованим. При ручному 
запуску багато можливостей програми будуть недоступні. 
Для запису макроса автоматизованим шляхом потрібно: 
відкрити додаток в якому будете працювати виберіть команду сервіс-макрос-почати 
запись, в полі ім’я макроса за замовченням визначається стандартне ім’я макроса Макрос1, 
його можна замінити будь-яким (не допускається використання в імені пробелів та крапок 
імя довжиною не більше 255 символів першим символом має бути буква або_)  20
призначити комбінацію клавіш(необов’язково) 
зберегти в (місце знаходження макроса ) 
задати опис макроса 
провести запис макроса 
зупинити запис макроса 
перевірити виконання макроса з допомогою комбінацій клавіш які йому назначили, 
або ж сервіс – макрос - макроси-вибрати зі списку потрібний-кн. Виконати. 
Щоб переглянути потрібний макрос в його лістинговому записі сервіс-макрос-
макроси-вибрати потрібний – изменить ( або команда войти) 
Макрос містить наступні елементи: 
Ключове слово Sub (subrontine - підпрограма) –позначає початок макроса End sub - 
кінець макроса відображається синім 
Ім’я макроса – оригінальна назва макроса що дозволяє його розпізнавати серед інших 
(за замовченням при авто записі Макрос1) чорним 
Коментарі – інформація про макрос та його команди, які від тіла програми 
відділяються ‘ ‘ відображається зеленим кольором 
Макрооператори - основне тіло макроса - складаються з послідовності операторів і 
являють собою інтерпретацію виконаних дій під час запису макроса. Відображаються 
чорним кольором. 
Редактор VBA містить наступні елементи вікно проектів – Project де відображаються 
модулі, форми, і об’єкти пакету Microsoft Office, вікно властивостей Properties - , вікно 
обробки подій який містить список об’єктів - Objekt list, список процедур – Procedure list. 
Макрос відображається в модулі. Модуль – це елемент VBA який містить запис 
кількох макросів чи процедур