Выборка из массива excel vba
Всем доброго дня, просьба помочь. Нужно на листе 1 в столбце "телефон" найти номер из листа 2 и если будет совпадение то в той строке где есть совпадение заменить значение из столбца источник на значение "директ"
Всем доброго дня, просьба помочь. Нужно на листе 1 в столбце "телефон" найти номер из листа 2 и если будет совпадение то в той строке где есть совпадение заменить значение из столбца источник на значение "директ" ches66
Sub tt()
With Sheets("Лист2")
n_ = .Cells(.Rows.Count, 1).End(3).Row
ar = .Cells(1).Resize(.Cells(.Rows.Count, 1).End(3).Row)
End With
d_ = "Директ"
n1_ = Cells(Rows.Count, 2).End(3).Row - 1
ar1 = Cells(2, 2).Resize(n1_)
ar2 = Cells(2, 3).Resize(n1_)
With CreateObject("Scripting.Dictionary")
'словарь нужен, чтобы убрать множественные дубликаты в листе2. Можно без этого, но на больших объемах лучше без дублей
'можно было удалить дубли сразу на листе 2, но можно ли там что-то менять? Не знаю
For r = 1 To n_
a = .Item(ar(r, 1))
Next r
nn_ = .Count - 1
For i = 1 To n1_
For j = 0 To nn_
If ar2(i, 1) <> d_ Then
If InStr(ar1(i, 1), .keys()(j)) Then
ar2(i, 1) = d_
End If
End If
Next j
Next i
End With
Cells(2, 3).Resize(n1_) = ar2
End Sub
Sub tt()
With Sheets("Лист2")
n_ = .Cells(.Rows.Count, 1).End(3).Row
ar = .Cells(1).Resize(.Cells(.Rows.Count, 1).End(3).Row)
End With
d_ = "Директ"
n1_ = Cells(Rows.Count, 2).End(3).Row - 1
ar1 = Cells(2, 2).Resize(n1_)
ar2 = Cells(2, 3).Resize(n1_)
With CreateObject("Scripting.Dictionary")
'словарь нужен, чтобы убрать множественные дубликаты в листе2. Можно без этого, но на больших объемах лучше без дублей
'можно было удалить дубли сразу на листе 2, но можно ли там что-то менять? Не знаю
For r = 1 To n_
a = .Item(ar(r, 1))
Next r
nn_ = .Count - 1
For i = 1 To n1_
For j = 0 To nn_
If ar2(i, 1) <> d_ Then
If InStr(ar1(i, 1), .keys()(j)) Then
ar2(i, 1) = d_
End If
End If
Next j
Next i
End With
Cells(2, 3).Resize(n1_) = ar2
End Sub
Sub tt()
With Sheets("Лист2")
n_ = .Cells(.Rows.Count, 1).End(3).Row
ar = .Cells(1).Resize(.Cells(.Rows.Count, 1).End(3).Row)
End With
d_ = "Директ"
n1_ = Cells(Rows.Count, 2).End(3).Row - 1
ar1 = Cells(2, 2).Resize(n1_)
ar2 = Cells(2, 3).Resize(n1_)
With CreateObject("Scripting.Dictionary")
'словарь нужен, чтобы убрать множественные дубликаты в листе2. Можно без этого, но на больших объемах лучше без дублей
'можно было удалить дубли сразу на листе 2, но можно ли там что-то менять? Не знаю
For r = 1 To n_
a = .Item(ar(r, 1))
Next r
nn_ = .Count - 1
For i = 1 To n1_
For j = 0 To nn_
If ar2(i, 1) <> d_ Then
If InStr(ar1(i, 1), .keys()(j)) Then
ar2(i, 1) = d_
End If
End If
Next j
Next i
End With
Cells(2, 3).Resize(n1_) = ar2
End Sub
_Boroda_, Спасибо . На вашем файле все сработало. Я добавил в файл еще строки , и перестало работать, но там есть пустые ячейки в столбце B , макрос их обработает ? Дубли конечно можно удалять. Главное сопоставить номера, и если есть то поменять в столбце C источник на "директ"
_Boroda_, Спасибо . На вашем файле все сработало. Я добавил в файл еще строки , и перестало работать, но там есть пустые ячейки в столбце B , макрос их обработает ? Дубли конечно можно удалять. Главное сопоставить номера, и если есть то поменять в столбце C источник на "директ" ches66
А поконкретнее? Что перестало, где перестало, как нужно, чтобы работало? Автор - _Boroda_
Дата добавления - 09.10.2018 в 09:27
_Boroda_, Итак в вашем файле который вы прислали нажимашь кнопку заполнить и все прекрасно работает, в какой строчке находит телефон меняет статус на "директ", я добавил к существующим строкам строки которые мне еще нужно обработать и теперь весь файл перестал обрабатываться - полностью. Нажимаешь кнопку и ничего не происходит. Файл с новымми строками прикреплял выше. В строках которые добавил встречаются пустые ячейки с незаполненным номером. Спасибо.
_Boroda_, Итак в вашем файле который вы прислали нажимашь кнопку заполнить и все прекрасно работает, в какой строчке находит телефон меняет статус на "директ", я добавил к существующим строкам строки которые мне еще нужно обработать и теперь весь файл перестал обрабатываться - полностью. Нажимаешь кнопку и ничего не происходит. Файл с новымми строками прикреплял выше. В строках которые добавил встречаются пустые ячейки с незаполненным номером. Спасибо. ches66
Кнропка "Вернуть" - это не для Вас, это мне возврат нужен был для проверки. Эта кнопка да, она на конкретный диапазон была (до строки 65), чтобы не морочиться, но Вас это вообще волновать не должно
Вам нужна кнопка "Заполнить", которая работает с любыми диапазонами.
Какая КОНКРЕТНО строчка Вас не устраивает? Где не меняет?
Держите на всякий случай файл с одной кнопкой и без возврата предыдущих значений.
Кнропка "Вернуть" - это не для Вас, это мне возврат нужен был для проверки. Эта кнопка да, она на конкретный диапазон была (до строки 65), чтобы не морочиться, но Вас это вообще волновать не должно
Вам нужна кнопка "Заполнить", которая работает с любыми диапазонами.
Какая КОНКРЕТНО строчка Вас не устраивает? Где не меняет?
Держите на всякий случай файл с одной кнопкой и без возврата предыдущих значений. _Boroda_
Держите на всякий случай файл с одной кнопкой и без возврата предыдущих значений. Автор - _Boroda_
Дата добавления - 09.10.2018 в 14:13
_Boroda_, Все извините проверил, блин я не с тем с сравнивал ,я сравнивал уже с переделанной таблицей . Все работает здорово! Спасибо большое!
_Boroda_, Все извините проверил, блин я не с тем с сравнивал ,я сравнивал уже с переделанной таблицей . Все работает здорово! Спасибо большое! ches66
Массивы – это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в VBA Excel, как и другие переменные, объявляются с помощью операторов Dim и Public. Для изменения размерности динамических массивов используется оператор ReDim. Массивы с заранее объявленной размерностью называют статическими.
Одномерные массивы
Объявление одномерных (линейных) статических массивов в VBA Excel:
В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.
По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1». Вместо верхнего индекса можно использовать переменную.
Многомерные массивы
Объявление многомерных статических массивов в VBA Excel аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:
Третий массив состоит из 10000 элементов — 10×10×10×10.
Динамические массивы
Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:
Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:
Переопределять размерность динамических массивов в процессе работы программы можно неоднократно, как по количеству измерений, так и по количеству элементов в измерении.
При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:
Обратите внимание!
Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.
Максимальный размер
Размер массива – это произведение длин всех его измерений. Он представляет собой общее количество элементов, содержащихся в данный момент в массиве.
По информации с сайта разработчиков, максимальный размер массивов зависит от операционной системы и доступного объема памяти. Использование массивов, размер которых превышает объем доступной оперативной памяти компьютера, приводит к снижению скорости, поскольку системе необходимо выполнять запись данных и чтение с диска.
Использование массивов
Приведу два примера, где не обойтись без массивов.
1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.
2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.
Функции Array, LBound, UBound
Функция Array
Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).
При написании макросов для работы с данными в VBA Excel иногда возникает необходимость отбора уникальных значений из списка с повторяющимися элементами. Для этого можно воспользоваться следующим кодом:
На этом отбор уникальных значений завершен. Коллекция заполнена уникальными элементами.
Добавление уникальных элементов в ListBox
Теперь можно добавить уникальные значения в ListBox, если перед этим создать форму UserForm1 и на нее добавить элемент управления ListBox1:
ListBox заполнен уникальными значениями из коллекции. Другие способы заполнения ListBox и ComboBox смотрите здесь.
Запись уникальных значений на рабочий лист
А так можно добавить уникальные элементы в ячейки столбца «В» активного рабочего листа:
При необходимости сортируем полученный список в столбце "В":
Range ( Cells ( 1 , 2 ) , Cells ( i , 2 ) ) . Sort Key1 : = Range ( "B1" ) , Order1 : = xlAscending , _
А также можно отобразить количество найденных уникальных элементов, если, конечно, на форму UserForm1 добавлен элемент управления Label1:
Если вам необходимо в ListBox или ComboBox загрузить отсортированный список, его элементы можно добавить с листа Excel после сортировки, в данном примере из диапазона Range(Cells(1, 2), Cells(i, 2)).
Обратите внимание, что в представленном коде VBA Excel для отбора уникальных значений из списка, выгрузки их в ListBox и записи на рабочий лист идет сплошная нумерация от Sub ОтборУникальных() и до End Sub.
Для наглядного ознакомления с работой представленного кода вы можете скачать демонстрационный файл.
Смотрите, как удалить повторяющиеся значения из диапазона ячеек в VBA Excel с помощью метода Range.RemoveDuplicates и отобрать уникальные значения из списка с помощью объекта Dictionary.
28 комментариев для “VBA Excel. Отбор уникальных значений с помощью Collection”
Можно ли сделать так :
Range ( Cells ( 1 , 2 ) , Cells ( i , 2 ) ) . Sort Key1 : = Range ( "А51" ) , Order1 : = xlAscending , _
у меня не получается, помогите пожалуйста, заранее спасибо!
С приветом Холмурод.
Привет, Холмурод. В примере указан диапазон из столбца «B»: Range(Cells(1, 2), Cells(i, 2)). Замените его на диапазон из столбца «A»: Range(Cells(1, 1), Cells(i, 1)).
Привет Евгений.
в ListBox1 список покажет но Sort Key1:=Range(«A30») в ячейке A30 ничего нет. Сделал вот так:
Range ( Cells ( 1 , 1 ) , Cells ( i , 1 ) ) . Sort Key1 : = Range ( "A30" ) , Order1 : = xlAscending , Header : = xlGuess , _
Холмурод, а вы хотите сделать, чтобы вставленный на лист список начинался с ячейки A30? Если да, то тогда выгрузку на лист надо начать с 30-й ячейки:
и для сортировки указать диапазон, начинающийся с 30-й ячейки:
Евгений огромное Вам спасибо.
просто получилось.
Добрый день,
В приведенном примере создаётся коллекция уникальных значений. Все работает. Только я не пойму чем, в какой строке определяется уникальность значений этих значений? Где происходит сравнение ? Ведь, наверное, в коллекцию должны записаться все элементы из Range(«A1:A20») ?
Привет, Антон.
Уникальность значений проверяется в строке добавления очередного элемента в коллекцию:
Первое выражение CStr(myCell.Value) определяет записываемый элемент в коллекцию, второе определяет добавляемый ключ. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибок, перед циклом добавлена строка:
Простое и элегантное решение поиска уникальных значений!
Спасибо!
Добрый день!
Странно у меня не срабатывает данный код и выдает ошибку «This key already associated with element of this collection»
Привет, Камалджан!
Строка
должна стоять перед строкой
чтобы пропускать ошибки “This key already associated with element of this collection”, как в исходном коде из этой статьи:
Евгений,
Снимаю шляпу…..
Тонко придумано.
Спасибо
Антон, это решение я подсмотрел у Джона Уокенбаха в книге «Excel 2010: профессиональное программирование на VBA».
Евгений, хорошо было бы привести пример со словарем в дополнение к коллекции.
И рассказать как-нибудь про метод Range.RemoveDuplicates
Привет, Фарин!
Согласен с вашими пожеланиями, принял их к сведению.
Добрый день!
Скажите пожалуйста, чувствителен ли данный метод добавления уникальных значений к регистру?
Я делаю выборку уникальных значений по полю, и значения «Сибирь» и «сибирь» почему то присваиваются одному значению «сибирь».
Может быть чувствительность как то отключается/включается?
Подскажите пожалуйста )
Добрый день, Андрей!
Ключи объекта Collection нечувствительны к регистру. Если вы хотите отобрать уникальные значения с учетом регистра, используйте код с объектом Dictionary.
Евгений, спасибо большое за ответ! Принял к использованию )
Здравствуйте, очень интересный метод, но это работает только на простом списке? то есть, можно ли применить этот метод для таблицы из 2-3 столбцов с проверкой на уникальность только по одному столбцу?
Здравствуйте, Вольдемар!
Можно удалить из таблицы строки с неуникальными значениями в одном столбце, проверяя уникальность значений снизу вверх:
мда, это ж надо так догадаться, спасибо, Евгений, я бы сам не додумался
Добрый день, а как сделать так, чтобы уникальные ячейки загружались на лист в определенный столбец?
В выражении i = 10 - 1 число 10 указывает, что заполнение столбца начнется с 10 строки. Если заполнение должно начаться с первой ячейки заданного столбца, это выражение следует исключить из процедуры.
В выражении Worksheets("Лист1").Cells(i, "D") = myElement :
- "Лист1" – наименование ярлыка листа, в ячейки которого будут вставляться уникальные значения;
- "D" – буквенное обозначение столбца, который будет заполняться (можно заменить на числовое значение: 1 вместо «A», 2 вместо «B» и т.д.).
Здравствуйте, помогите пожалуйста нужен код для получения уникального списка по критерию в соседнем столбце
Добрый день. Помогите пож. «Играюсь» с Collection. При попытке вывести кол-во членов коллекции через Collection.Count Программа выдает кол-во строк указанного диапазона(( , а не кол-во знаков в диапазоне. И только повтором типа myCollection.Add CStr(myCell.Value), CStr(myCell.Value) MsgBox выдает кол-во членов коллекции корректно. Почему??((
Здравствуйте, Дитрий!
Пример кода, пожалуйста.
Евгений, добрый день! Только начинаю изучать VBA. Вопросов. Постараюсь в будущем по пустякам не отвлекать).
По существу дела:
MyColl . Add CStr ( c . Value ) , CStr ( c . Value ) 'только повторение CStr(c.Value) дает нужные данные
Хочется узнать:
1. Почему только повторение CStr(c.Value) дает кол-во уникальных знаков, в противном случае дает значение 20 (я почему жду в результате кол-во всех знаков, но без учета пустых ячеек);
2. К моему удивлению макрос выдает данные только с типом возвращ. значений CStr;
3. И если не сложно подсказать куда двигаться: Возможно ли в коллекцию отбирать уже данные с нужными параметрами или для этого нужно использовать другие инструменты?
За ранее, огромное спс.
Добрый день, Дитрий!
1. Первое выражение CStr(c.Value) определяет записываемый элемент в коллекцию, второе определяет добавляемый ключ. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибок, перед циклом добавлена строка: On Error Resume Next .
2. Элементы коллекции могут содержать разные типы данных, если их не преобразовывать в текст: MyColl.Add c.Value .
3. Я использую коллекции только для отбора уникальных значений, в остальных случаях — массивы. Копирование значений из диапазона ячеек в массив и обратно.
Евгений, от души! Все ясно, я получил исчерпывающий ответ.
P.S. Спасибо за Вашу работу! Этот один из лучших обучающих ресурсов.
Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Этот замечательный цикл применяется, когда неизвестно количество элементов в группе и их индексация, в противном случае, более предпочтительным считается использование цикла For…Next.
Синтаксис цикла For Each… Next
В квадратных скобках указаны необязательные атрибуты цикла For Each… Next.
Компоненты цикла For Each… Next
Компонент | Описание |
---|---|
element | Обязательный атрибут в операторе For Each, необязательный атрибут в операторе Next. Представляет из себя переменную, используемую для циклического прохода элементов группы (диапазон, массив, коллекция), которая предварительно должна быть объявлена с соответствующим типом данных*. |
group | Обязательный атрибут. Группа элементов (диапазон, массив, коллекция), по каждому элементу которой последовательно проходит цикл For Each… Next. |
statements | Необязательный** атрибут. Операторы вашего кода. |
Exit For | Необязательный атрибут. Оператор выхода из цикла до его окончания. |
*Если цикл For Each… Next используется в VBA Excel для прохождения элементов коллекции (объект Collection) или массива, тогда переменная element должна быть объявлена с типом данных Variant, иначе цикл работать не будет.
**Если не использовать в цикле свой код, смысл применения цикла теряется.
Примеры циклов For Each… Next
Цикл для диапазона ячеек
На активном листе рабочей книги Excel выделите диапазон ячеек и запустите на выполнение следующую процедуру:
Информационное окно MsgBox выведет адреса выделенных ячеек и их содержимое, если оно есть. Если будет выбрано много ячеек, то полностью информация по всем ячейкам выведена не будет, так как максимальная длина параметра Prompt функции MsgBox составляет примерно 1024 знака.
Цикл для коллекции листов
Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:
Информационное окно MsgBox выведет список наименований всех листов рабочей книги Excel по порядковому номеру их ярлычков, соответствующих их индексам.
Цикл для массива
Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.
Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.
Этот код, как и все остальные в этой статье, тестировался в Excel 2016.
Цикл для коллекции подкаталогов и выход из цикла
В результате работы программы будут выведены не только наименования подкаталогов, видимых при переходе в проводнике к диску C, но и скрытые и служебные папки. Для просмотра списка всех подкаталогов на диске C, закомментируйте участок кода от If до End If включительно и запустите выполнение процедуры в редакторе VBA Excel.
20 комментариев для “VBA Excel. Цикл For Each… Next”
Здравствуйте!
Я новичок в VBA, поэтому ,возможно, вопрос глупый, но не могу никак придумать процедуру для сравнения двух массивов с помощью цикла For Each… Next.
т.е. имеются столбцы A:B — ФИО1 Дата1 и столбцы C:D — ФИО2 Дата2. Хочу сравнить и при совпадении выделить совпадающие ячейки цветом.
Подскажите, хотя бы примерно, как это сделать.
Спасибо
Здравствуйте!
Диапазоны в примере одинаковые по размеру, но могут быть любые:
У меня к Вам вопрос, покажите дилетанту как быть в этой ситуации!
Есть файл в нем 2 листа, в первом массив данных во втором выборка из массива
Сделал через IF но это только для 4 запросов, а если их будет 100 ? Программа запретит писать такую большую формулу!
Задача: Как сделать так, что бы при выборе определенной позиции данные брались из массива который в Input Data. Через VLOOKUP не получается сделать, потому что он берет данные по первому совпадению.
Число строк будет одинаковым для всех позиций! Тобишь для всех запросов будет отображаться по определенному количеству строк. В примере рассматривается по 3 строки
У меня к Вам вопрос, покажите дилетанту как быть в этой ситуации!
Есть файл в нем 2 листа, в первом массив данных во втором выборка из массива
Сделал через IF но это только для 4 запросов, а если их будет 100 ? Программа запретит писать такую большую формулу!
Задача: Как сделать так, что бы при выборе определенной позиции данные брались из массива который в Input Data. Через VLOOKUP не получается сделать, потому что он берет данные по первому совпадению.
Число строк будет одинаковым для всех позиций! Тобишь для всех запросов будет отображаться по определенному количеству строк. В примере рассматривается по 3 строки Gopronotmore
У меня к Вам вопрос, покажите дилетанту как быть в этой ситуации!
Есть файл в нем 2 листа, в первом массив данных во втором выборка из массива
Сделал через IF но это только для 4 запросов, а если их будет 100 ? Программа запретит писать такую большую формулу!
Задача: Как сделать так, что бы при выборе определенной позиции данные брались из массива который в Input Data. Через VLOOKUP не получается сделать, потому что он берет данные по первому совпадению.
Число строк будет одинаковым для всех позиций! Тобишь для всех запросов будет отображаться по определенному количеству строк. В примере рассматривается по 3 строки Автор - Gopronotmore
Дата добавления - 29.06.2015 в 16:42
=ИНДЕКС('Input Data'!C$2:C$30;НАИМЕНЬШИЙ(ЕСЛИ('Input Data'!$B$2:$B$30=Form!$B$2;СТРОКА('Input Data'!B$2:B$30)-1;"");СТРОКА(A1)))
=ИНДЕКС('Input Data'!C$2:C$30;НАИМЕНЬШИЙ(ЕСЛИ('Input Data'!$B$2:$B$30=Form!$B$2;СТРОКА('Input Data'!B$2:B$30)-1;"");СТРОКА(A1)))
=ИНДЕКС('Input Data'!C$2:C$30;НАИМЕНЬШИЙ(ЕСЛИ('Input Data'!$B$2:$B$30=Form!$B$2;СТРОКА('Input Data'!B$2:B$30)-1;"");СТРОКА(A1)))
формула массива, вводится сочетанием ctrl+shift+enter Автор - Manyasha
Дата добавления - 29.06.2015 в 16:56
Большое спасибо! Мне не критично! Буду пытаться адаптировать Вашу формулу к своему файлу! Тему не закрывайте, если будут вопросы, можно будет тут отписаться!
Большое спасибо! Мне не критично! Буду пытаться адаптировать Вашу формулу к своему файлу! Тему не закрывайте, если будут вопросы, можно будет тут отписаться!
Спасибо! Автор - Gopronotmore
Дата добавления - 29.06.2015 в 17:02
А можете расписать как эта формула работает ? Разобрался, и применил, но вот смысла не понимаю! Напишите если не трудно в чем принцип работы ? Понимаю что берет данные из массива, но не могу понять для чего ROW и SMALL как они оказывают влияние на выводимый результат? Спасибо!
А можете расписать как эта формула работает ? Разобрался, и применил, но вот смысла не понимаю! Напишите если не трудно в чем принцип работы ? Понимаю что берет данные из массива, но не могу понять для чего ROW и SMALL как они оказывают влияние на выводимый результат? Спасибо! Gopronotmore
возвращает массив, состоящий из номеров строк, для которых названия машин равны названию В2 на листе Form, остальные элементы массива приравниваются к "" (пустая строка). Т.е для "машина2" массив будет
Такая нумерация не подходит для нашей функции INDEX(), т.к. 1-й элемент массива 'Input Data'!C$2:C$30 расположен на 2-й строке листа (функция Row возвращает номер строки именно на листе). Отнимем 1 от возвращаемого номера строки, чтобы полученный номер совпадал с номером элемента массива, для "машина2" массив будет
Данная формула НЕ протягиваемая, если Вы запишите ее в одну ячейку и будете тянуть вниз, возвращаться у Вас будет все время первый элемент массива ("").
Для того чтобы формула возвращала элементы со сдвигом и игнорировала элементы = "", используем функцию SMALL(, k) - посмотрите в описании, как она работает, если не понятно.
Для первой ячейки, где мы записали формулу k=ROW(A1) = 1. Т.к. ссылка относительная, k будет меняться при протягивании, т.е. во второй ячейке Вы увидите уже ROW(A2) = 2.
Итак, после применения функции SMALL наш массив уже будет таким:
Функцию INDEX() тоже посмотрите в описании, она работает уже с последним массивом.
Надеюсь понятно объяснила, кстати, посмотрите еще Как разобраться в работе сложной формулы?
возвращает массив, состоящий из номеров строк, для которых названия машин равны названию В2 на листе Form, остальные элементы массива приравниваются к "" (пустая строка). Т.е для "машина2" массив будет
Такая нумерация не подходит для нашей функции INDEX(), т.к. 1-й элемент массива 'Input Data'!C$2:C$30 расположен на 2-й строке листа (функция Row возвращает номер строки именно на листе). Отнимем 1 от возвращаемого номера строки, чтобы полученный номер совпадал с номером элемента массива, для "машина2" массив будет
Данная формула НЕ протягиваемая, если Вы запишите ее в одну ячейку и будете тянуть вниз, возвращаться у Вас будет все время первый элемент массива ("").
Для того чтобы формула возвращала элементы со сдвигом и игнорировала элементы = "", используем функцию SMALL(, k) - посмотрите в описании, как она работает, если не понятно.
Для первой ячейки, где мы записали формулу k=ROW(A1) = 1. Т.к. ссылка относительная, k будет меняться при протягивании, т.е. во второй ячейке Вы увидите уже ROW(A2) = 2.
Итак, после применения функции SMALL наш массив уже будет таким:
Функцию INDEX() тоже посмотрите в описании, она работает уже с последним массивом.
возвращает массив, состоящий из номеров строк, для которых названия машин равны названию В2 на листе Form, остальные элементы массива приравниваются к "" (пустая строка). Т.е для "машина2" массив будет
Такая нумерация не подходит для нашей функции INDEX(), т.к. 1-й элемент массива 'Input Data'!C$2:C$30 расположен на 2-й строке листа (функция Row возвращает номер строки именно на листе). Отнимем 1 от возвращаемого номера строки, чтобы полученный номер совпадал с номером элемента массива, для "машина2" массив будет
Данная формула НЕ протягиваемая, если Вы запишите ее в одну ячейку и будете тянуть вниз, возвращаться у Вас будет все время первый элемент массива ("").
Для того чтобы формула возвращала элементы со сдвигом и игнорировала элементы = "", используем функцию SMALL(, k) - посмотрите в описании, как она работает, если не понятно.
Для первой ячейки, где мы записали формулу k=ROW(A1) = 1. Т.к. ссылка относительная, k будет меняться при протягивании, т.е. во второй ячейке Вы увидите уже ROW(A2) = 2.
Итак, после применения функции SMALL наш массив уже будет таким:
Функцию INDEX() тоже посмотрите в описании, она работает уже с последним массивом.
Надеюсь понятно объяснила, кстати, посмотрите еще Как разобраться в работе сложной формулы? Автор - Manyasha
Дата добавления - 30.06.2015 в 12:39
Подскажите как реализовать ?
Здравствуйте в написание кода я не силен, только начал осваивать.
подскажите как реализовать
Есть БД в Excel
в первом столбце есть номер закупки второй и третий пустые. ( но есть документ на бумаге где у номера закупки есть свой порядковый номер например закуп 43 порядковый номер 1 44 порядковый номер 2, 46 порядковый номер 3 и т.д. Эти номера закупок повторяются в разбросе по всему первому столбцу) всего скажем номеров закупок 120 У каждой закупки свой порядковый номер - хочу забить порядковый номер закупки в третий столбец).
Во втором столбце сделать выбор например проверить первый столбец на закуп 43, 44, 46 и тд если нашел значения 43, 44, 46 то во втором столбце напротив тех значений ввести порядковые номера соответствующие закупу 1,2,3
С чего начать ??
подскажите
Подскажите как реализовать ?
Здравствуйте в написание кода я не силен, только начал осваивать.
подскажите как реализовать
Есть БД в Excel
в первом столбце есть номер закупки второй и третий пустые. ( но есть документ на бумаге где у номера закупки есть свой порядковый номер например закуп 43 порядковый номер 1 44 порядковый номер 2, 46 порядковый номер 3 и т.д. Эти номера закупок повторяются в разбросе по всему первому столбцу) всего скажем номеров закупок 120 У каждой закупки свой порядковый номер - хочу забить порядковый номер закупки в третий столбец).
Во втором столбце сделать выбор например проверить первый столбец на закуп 43, 44, 46 и тд если нашел значения 43, 44, 46 то во втором столбце напротив тех значений ввести порядковые номера соответствующие закупу 1,2,3
С чего начать ??
подскажите ABVS
Здравствуйте в написание кода я не силен, только начал осваивать.
подскажите как реализовать
Есть БД в Excel
в первом столбце есть номер закупки второй и третий пустые. ( но есть документ на бумаге где у номера закупки есть свой порядковый номер например закуп 43 порядковый номер 1 44 порядковый номер 2, 46 порядковый номер 3 и т.д. Эти номера закупок повторяются в разбросе по всему первому столбцу) всего скажем номеров закупок 120 У каждой закупки свой порядковый номер - хочу забить порядковый номер закупки в третий столбец).
Во втором столбце сделать выбор например проверить первый столбец на закуп 43, 44, 46 и тд если нашел значения 43, 44, 46 то во втором столбце напротив тех значений ввести порядковые номера соответствующие закупу 1,2,3
С чего начать ??
подскажите Автор - ABVS
Дата добавления - 14.01.2016 в 08:47
Читайте также: