Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Использование в VBA В итерации значений ИндексПоискПоз, Пере - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Использование в VBA В итерации значений ИндексПоискПоз, Пере (Макросы/Sub)
Использование в VBA В итерации значений ИндексПоискПоз, Пере
lostandleft Дата: Понедельник, 23.09.2019, 18:34 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
Прошу Вас помочь с вопросом по созданию функций: нужен полный аналог функции Индекс(диапазон;Поискпоз(Ячейка;Диапазон поиска;0);Поискпоз(ЗначениеЛистБокс[i];Диапазон столбца;0))

Суть задачи.
Дано меню ЛистБокс2, в нем собираются критерии для поиска по столбцам значений.
Критериев может быть несколько, необходимо собрать формулу на VBA которая в цикле (цикл зависит от количества значений в листбокс2) просуммирует все значения и выдаст результат.
Критерии - Это заголовки таблицы

Не могу разобраться с базовыми понятиями того, как именно в функцию передать значения из ячейки.

Примерное, схематичное решение вижу так:
Количество Значений в ЛистБоксе определяет количество итераций в цикле.
1 - Создаем массив данных, Размерность массива определяется колчеством значений в ЛистБокс
2 - Используем Функцию : Индекс(ИндексируемыйДиапазон;Поискпоз(ВыделяемЯчейкуПоиска;ДиапазонПоискаСтроки;0);Поискпоз(ЗначениеЛистБокс[i];ДиапазонПоискаСтолбца;0))
3 - Найденное Значение записываем в Массив,
4 - Считаем сумму значений Массива, и Записываем как результат в ячейку.
Как все это реализовать технически ума не хватает.
не понимаю базового принципа того, как свои значения подставить через WorksheetFunction
Если Возможно, пожалуйста подскажите.
Я очень старался, читал много тем, но не получается.

VBA что-то очень тяжело дается, с формулами могу реализовать что угодно, но тут именно итерация нужна. листБоксы создал, набор данных в них получил. Дальше ну никак.
[vba]
Код

Function ПоискП(Table As Range,) '...Дальше ума не хватило

Dim i As Long
Dim result As String

' Создаем массив данных для хранения найденной позиции
Dim MyArray(0 To ListBox2.ListCount) As Variant

' Работаем с итерациями (количество значений в листбоксе - количество итераций)
For i = 1 To ListBox2.ListCount

'Тут передаем найденное значение в Массив.
MyArray(i) = i

Exit For

' Тут вытаскиваем из массива данные, и суммируем, и передаем в Результат

For i = 1 To ListBox2.ListCount
s = s + MyArray(i)
Next

result = s

ПоискП = CLng(result)
End Sub
[/vba]

Ради бога извините но я не могу прикрепить фаил, он размером больше положенного
Поэтому прикрепляю сторонним сервисом
https://u.to/LSlhFg
К сообщению приложен файл: 1670083.xls(69.0 Kb)


Сообщение отредактировал lostandleft - Вторник, 24.09.2019, 14:16
 
Ответить
СообщениеДобрый день!
Прошу Вас помочь с вопросом по созданию функций: нужен полный аналог функции Индекс(диапазон;Поискпоз(Ячейка;Диапазон поиска;0);Поискпоз(ЗначениеЛистБокс[i];Диапазон столбца;0))

Суть задачи.
Дано меню ЛистБокс2, в нем собираются критерии для поиска по столбцам значений.
Критериев может быть несколько, необходимо собрать формулу на VBA которая в цикле (цикл зависит от количества значений в листбокс2) просуммирует все значения и выдаст результат.
Критерии - Это заголовки таблицы

Не могу разобраться с базовыми понятиями того, как именно в функцию передать значения из ячейки.

Примерное, схематичное решение вижу так:
Количество Значений в ЛистБоксе определяет количество итераций в цикле.
1 - Создаем массив данных, Размерность массива определяется колчеством значений в ЛистБокс
2 - Используем Функцию : Индекс(ИндексируемыйДиапазон;Поискпоз(ВыделяемЯчейкуПоиска;ДиапазонПоискаСтроки;0);Поискпоз(ЗначениеЛистБокс[i];ДиапазонПоискаСтолбца;0))
3 - Найденное Значение записываем в Массив,
4 - Считаем сумму значений Массива, и Записываем как результат в ячейку.
Как все это реализовать технически ума не хватает.
не понимаю базового принципа того, как свои значения подставить через WorksheetFunction
Если Возможно, пожалуйста подскажите.
Я очень старался, читал много тем, но не получается.

VBA что-то очень тяжело дается, с формулами могу реализовать что угодно, но тут именно итерация нужна. листБоксы создал, набор данных в них получил. Дальше ну никак.
[vba]
Код

Function ПоискП(Table As Range,) '...Дальше ума не хватило

Dim i As Long
Dim result As String

' Создаем массив данных для хранения найденной позиции
Dim MyArray(0 To ListBox2.ListCount) As Variant

' Работаем с итерациями (количество значений в листбоксе - количество итераций)
For i = 1 To ListBox2.ListCount

'Тут передаем найденное значение в Массив.
MyArray(i) = i

Exit For

' Тут вытаскиваем из массива данные, и суммируем, и передаем в Результат

For i = 1 To ListBox2.ListCount
s = s + MyArray(i)
Next

result = s

ПоискП = CLng(result)
End Sub
[/vba]

Ради бога извините но я не могу прикрепить фаил, он размером больше положенного
Поэтому прикрепляю сторонним сервисом
https://u.to/LSlhFg

Автор - lostandleft
Дата добавления - 23.09.2019 в 18:34
Pelena Дата: Вторник, 24.09.2019, 11:08 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 14585
Репутация: 3196 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
lostandleft, так ли необходимо использовать два листбокса? Может, достаточно в одном флажками выбрать нужные позиции? Или вообще обойтись флажками на листе, а расчёт сделать формулами? Или у Вас ещё какие-то планы на эти листбоксы?

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


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеlostandleft, так ли необходимо использовать два листбокса? Может, достаточно в одном флажками выбрать нужные позиции? Или вообще обойтись флажками на листе, а расчёт сделать формулами? Или у Вас ещё какие-то планы на эти листбоксы?

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

Автор - Pelena
Дата добавления - 24.09.2019 в 11:08
lostandleft Дата: Вторник, 24.09.2019, 14:24 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Обновил вложение.

так ли необходимо использовать два листбокса? Может, достаточно в одном флажками выбрать нужные позиции? Или вообще обойтись флажками на листе, а расчёт сделать формулами? Или у Вас ещё какие-то планы на эти листбоксы?


Для меня принципиально желтое в файле создать формулой. Так как строк может быть много, и к тому же столбцы могут меняться местами.
Поэтому я подумал о возможности написания собственной функции в ВБА

Будет реализация через ЛистБокс, или через выключатели, для меня не принципиально. Но. В реальном документе складов будет более 20.
И выбирать галочки было бы не очень удобно. Поэтому сделан ЛистБокс, в который и выбираем те склады, которые мы хотим просуммировать.
ЛистБоксы нужны только для набора складов, никаких других идей для них нет.

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

так ли необходимо использовать два листбокса? Может, достаточно в одном флажками выбрать нужные позиции? Или вообще обойтись флажками на листе, а расчёт сделать формулами? Или у Вас ещё какие-то планы на эти листбоксы?


Для меня принципиально желтое в файле создать формулой. Так как строк может быть много, и к тому же столбцы могут меняться местами.
Поэтому я подумал о возможности написания собственной функции в ВБА

Будет реализация через ЛистБокс, или через выключатели, для меня не принципиально. Но. В реальном документе складов будет более 20.
И выбирать галочки было бы не очень удобно. Поэтому сделан ЛистБокс, в который и выбираем те склады, которые мы хотим просуммировать.
ЛистБоксы нужны только для набора складов, никаких других идей для них нет.

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

Автор - lostandleft
Дата добавления - 24.09.2019 в 14:24
Gustav Дата: Вторник, 24.09.2019, 16:42 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1683
Репутация: 683 ±
Замечаний: 0% ±

начинал с Excel 4.0...
На листе "Склады" дал примерное решение на формулах. Функция СУММПРОИЗВ - наше всё! (см. в колонке L). Склады "выбираются" в колонке O путем проставления в ячейках любого символа. При этом над колонкой склада в диапазоне D1:K1 загораются "иксики" как символ выбора (трансляция через функцию ТРАНСП).

Никаких тебе явных итераций и нудятины с ЛистБоксом (уж, извините! :) ). Если надо отобразить значение из колонки L на другом листе, то можно вполне обойтись простым ВПРом или простым ИНДЕКС/ПОИСКПОЗ, без монстроидальных формул с их неоднократным использованием. Может, сгодится механизм в таком прозрачном виде? Понятно, что список выбора склада можно вынести на другой лист, а результатом из колонки L воспользоваться еще на каком-нибудь третьем.
К сообщению приложен файл: MultiStores.xls(66.5 Kb)


Мой tip box - яд 41001663842605
 
Ответить
СообщениеНа листе "Склады" дал примерное решение на формулах. Функция СУММПРОИЗВ - наше всё! (см. в колонке L). Склады "выбираются" в колонке O путем проставления в ячейках любого символа. При этом над колонкой склада в диапазоне D1:K1 загораются "иксики" как символ выбора (трансляция через функцию ТРАНСП).

Никаких тебе явных итераций и нудятины с ЛистБоксом (уж, извините! :) ). Если надо отобразить значение из колонки L на другом листе, то можно вполне обойтись простым ВПРом или простым ИНДЕКС/ПОИСКПОЗ, без монстроидальных формул с их неоднократным использованием. Может, сгодится механизм в таком прозрачном виде? Понятно, что список выбора склада можно вынести на другой лист, а результатом из колонки L воспользоваться еще на каком-нибудь третьем.

Автор - Gustav
Дата добавления - 24.09.2019 в 16:42
lostandleft Дата: Вторник, 24.09.2019, 19:49 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
На листе "Склады" дал примерное решение на формулах. Функция СУММПРОИЗВ - наше всё! (см. в колонке L). Склады "выбираются" в колонке O путем проставления в ячейках любого символа. При этом над колонкой склада в диапазоне D1:K1 загораются "иксики" как символ выбора (трансляция через функцию ТРАНСП).


Интересное решение, проблема лишь в том, что склады динамичные. В том файле что я отправил на файлообменник это хорошо видно.
Таблица сводная со складами формируется из косого-кривого документа 1с. Лист Склады - динамичный лист, он меняетя. это сводная таблица.
Сегодня задействовано 15 складов, а завтра все 24.
И строк сегодня 129 завтра 312.

Спасибо за попытку, за Ваше время. Если ничего лучше через VBA не подскажет коллективный разум, буду пробовать Ваш вариант.
 
Ответить
Сообщение
На листе "Склады" дал примерное решение на формулах. Функция СУММПРОИЗВ - наше всё! (см. в колонке L). Склады "выбираются" в колонке O путем проставления в ячейках любого символа. При этом над колонкой склада в диапазоне D1:K1 загораются "иксики" как символ выбора (трансляция через функцию ТРАНСП).


Интересное решение, проблема лишь в том, что склады динамичные. В том файле что я отправил на файлообменник это хорошо видно.
Таблица сводная со складами формируется из косого-кривого документа 1с. Лист Склады - динамичный лист, он меняетя. это сводная таблица.
Сегодня задействовано 15 складов, а завтра все 24.
И строк сегодня 129 завтра 312.

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

Автор - lostandleft
Дата добавления - 24.09.2019 в 19:49
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Использование в VBA В итерации значений ИндексПоискПоз, Пере (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!