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

Вход

Регистрация

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

 

= Мир MS Excel/Отфильтровать список по значению из другой ячейки скриптом - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Отфильтровать список по значению из другой ячейки скриптом (Формулы/Formulas)
Отфильтровать список по значению из другой ячейки скриптом
tnt Дата: Воскресенье, 24.01.2021, 18:03 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте! Помогите найти решение, как можно сделать фильтрацию нужного диапазона значений.
Имею таблицу: https://u.to/Ri-wGg

В ней 3 листа,
Лист "History" - база данных.
Лист "Positions" - сводка данных, результат работы скрипта. Здесь выпадающий список, в зависимости от того что выбрано, это и отображается.
Лист "Settings" - Настройки списка

Сейчас скрипт выдает все что в "History" без фильтрации по столбцу Broker.
Нужно так: выбираю на листе "Positions" в списке: 1 и ниже отображается все, что в "History" есть со значением в столбце Broker: 1

Скрипт выводит результат в ячейку A3 на листе "Positions" (=MyPortfolio(History!C3:C1000,History!E3:E1000)

Сам скрипт:

[vba]
Код
function MyPortfolio(tickers, values) {

var total = []
var sums = {}

for(i = 0; i < tickers.length; i++){
var t = tickers[i].toString()

if (t != "Cash"){

if (t in sums){
sums[t] += Number(values[i])
}
else{
sums[t] = Number(values[i])
}
}
}

for(var ticker in sums){
if(sums[ticker]>0){
total.push([ticker, sums[ticker]])
}
}
return total
}
[/vba]


Сообщение отредактировал tnt - Воскресенье, 24.01.2021, 18:07
 
Ответить
СообщениеЗдравствуйте! Помогите найти решение, как можно сделать фильтрацию нужного диапазона значений.
Имею таблицу: https://u.to/Ri-wGg

В ней 3 листа,
Лист "History" - база данных.
Лист "Positions" - сводка данных, результат работы скрипта. Здесь выпадающий список, в зависимости от того что выбрано, это и отображается.
Лист "Settings" - Настройки списка

Сейчас скрипт выдает все что в "History" без фильтрации по столбцу Broker.
Нужно так: выбираю на листе "Positions" в списке: 1 и ниже отображается все, что в "History" есть со значением в столбце Broker: 1

Скрипт выводит результат в ячейку A3 на листе "Positions" (=MyPortfolio(History!C3:C1000,History!E3:E1000)

Сам скрипт:

[vba]
Код
function MyPortfolio(tickers, values) {

var total = []
var sums = {}

for(i = 0; i < tickers.length; i++){
var t = tickers[i].toString()

if (t != "Cash"){

if (t in sums){
sums[t] += Number(values[i])
}
else{
sums[t] = Number(values[i])
}
}
}

for(var ticker in sums){
if(sums[ticker]>0){
total.push([ticker, sums[ticker]])
}
}
return total
}
[/vba]

Автор - tnt
Дата добавления - 24.01.2021 в 18:03
Gustav Дата: Понедельник, 25.01.2021, 09:59 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В ячейку A3 на листе на листе "Positions" вместо имеющейся формулы:
[vba]
Код
=MyPortfolio(
History!C3:C1000,
History!E3:E1000
)
[/vba]поместите новую:
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="Все",History!B3:B1000,History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="Все",History!B3:B1000,History!B3:B1000=E1))
)
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВ ячейку A3 на листе на листе "Positions" вместо имеющейся формулы:
[vba]
Код
=MyPortfolio(
History!C3:C1000,
History!E3:E1000
)
[/vba]поместите новую:
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="Все",History!B3:B1000,History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="Все",History!B3:B1000,History!B3:B1000=E1))
)
[/vba]

Автор - Gustav
Дата добавления - 25.01.2021 в 09:59
tnt Дата: Понедельник, 25.01.2021, 11:36 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, Благодарю! Думал, что уже без редактирования скрипта не получится.

Но возникла другая проблема, и я не понимаю по какой логике.
Теперь если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает. С чем это связано?
 
Ответить
СообщениеGustav, Благодарю! Думал, что уже без редактирования скрипта не получится.

Но возникла другая проблема, и я не понимаю по какой логике.
Теперь если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает. С чем это связано?

Автор - tnt
Дата добавления - 25.01.2021 в 11:36
Gustav Дата: Понедельник, 25.01.2021, 12:36 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает.

Вот так работает (я уже заменил в Вашей таблице):
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="Все",ROW(History!B3:B1000),History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="Все",ROW(History!B3:B1000),History!B3:B1000=E1))
)
[/vba]
Связано это с тем, что второй аргумент функции IF должен быть набором либо логических значений, либо чисел (потому что любое число, не равное 0 - это TRUE, а 0 - это FALSE). Поэтому пока в первом варианте там были числа хватало просто ссылки History!B3:B1000, в ячейках которой были числа (>0 = TRUE). Для текста же такая автоматическая замена не работает. Поэтому надо каким-то образом "превратить" значения этих ячеек либо в положительные числа, либо в TRUE. Я использовал функцию ROW для превращения в числа. Вместо ROW можно было бы использовать, например, ISTEXT или LEN. Наверняка можно еще какие-нибудь вспомнить...

Кстати, вот еще забавный вариант, который, наверное, вообще надо было предложить раньше всех :)
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="ВСЕ",History!B3:B1000<>E1,History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="ВСЕ",History!B3:B1000<>E1,History!B3:B1000=E1))
)
[/vba]


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Понедельник, 25.01.2021, 12:56
 
Ответить
Сообщение
если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает.

Вот так работает (я уже заменил в Вашей таблице):
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="Все",ROW(History!B3:B1000),History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="Все",ROW(History!B3:B1000),History!B3:B1000=E1))
)
[/vba]
Связано это с тем, что второй аргумент функции IF должен быть набором либо логических значений, либо чисел (потому что любое число, не равное 0 - это TRUE, а 0 - это FALSE). Поэтому пока в первом варианте там были числа хватало просто ссылки History!B3:B1000, в ячейках которой были числа (>0 = TRUE). Для текста же такая автоматическая замена не работает. Поэтому надо каким-то образом "превратить" значения этих ячеек либо в положительные числа, либо в TRUE. Я использовал функцию ROW для превращения в числа. Вместо ROW можно было бы использовать, например, ISTEXT или LEN. Наверняка можно еще какие-нибудь вспомнить...

Кстати, вот еще забавный вариант, который, наверное, вообще надо было предложить раньше всех :)
[vba]
Код
=MyPortfolio(
FILTER(History!C3:C1000,IF(E1="ВСЕ",History!B3:B1000<>E1,History!B3:B1000=E1)),
FILTER(History!E3:E1000,IF(E1="ВСЕ",History!B3:B1000<>E1,History!B3:B1000=E1))
)
[/vba]

Автор - Gustav
Дата добавления - 25.01.2021 в 12:36
tnt Дата: Понедельник, 25.01.2021, 13:39 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, все работает как нужно, еще раз благодарю Вас за помощь и подробное разъяснение!
 
Ответить
СообщениеGustav, все работает как нужно, еще раз благодарю Вас за помощь и подробное разъяснение!

Автор - tnt
Дата добавления - 25.01.2021 в 13:39
tnt Дата: Пятница, 05.02.2021, 12:11 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А как можно фильтрацию по значениям разложить еще на категории?
Например сейчас в таблице можно выбирать "Портфель" и сделать фильтрацию, а как добавить к каждому портфелю еще и категории? Только через усложнение формулы через ЕСЛИ?

Более наглядно:
https://docs.google.com/spreads....8068068

На листе "History" создается запись (строка), которая прикрепляется к определенному портфелю и категории этого портфеля. Список Портфелей и категорий прописан на листе "Settings".
На листе "Positions" должна происходить фильтрация по выбранному портфелю и его категории. Как можно это осуществить?
 
Ответить
СообщениеА как можно фильтрацию по значениям разложить еще на категории?
Например сейчас в таблице можно выбирать "Портфель" и сделать фильтрацию, а как добавить к каждому портфелю еще и категории? Только через усложнение формулы через ЕСЛИ?

Более наглядно:
https://docs.google.com/spreads....8068068

На листе "History" создается запись (строка), которая прикрепляется к определенному портфелю и категории этого портфеля. Список Портфелей и категорий прописан на листе "Settings".
На листе "Positions" должна происходить фильтрация по выбранному портфелю и его категории. Как можно это осуществить?

Автор - tnt
Дата добавления - 05.02.2021 в 12:11
Gustav Дата: Пятница, 05.02.2021, 17:18 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Только через усложнение формулы через ЕСЛИ?

Через добавление еще одного ЕСЛИ (условия) в каждый ФИЛЬТР:
[vba]
Код
=MyPortfolio(
FILTER(History!D3:D,
IF(E1=Settings!A2,ROW(History!B3:B),History!B3:B=E1),
IF(G1=Settings!B2,ROW(History!C3:C),History!C3:C=G1)
),
FILTER(History!F3:F,
IF(E1=Settings!A2,ROW(History!B3:B),History!B3:B=E1),
IF(G1=Settings!B2,ROW(History!C3:C),History!C3:C=G1)
)
)
[/vba]
Зарядил эту формулу в вашу таблицу, проверяйте (лист копии "Позиций" со старой формулой сохранил рядом).


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Только через усложнение формулы через ЕСЛИ?

Через добавление еще одного ЕСЛИ (условия) в каждый ФИЛЬТР:
[vba]
Код
=MyPortfolio(
FILTER(History!D3:D,
IF(E1=Settings!A2,ROW(History!B3:B),History!B3:B=E1),
IF(G1=Settings!B2,ROW(History!C3:C),History!C3:C=G1)
),
FILTER(History!F3:F,
IF(E1=Settings!A2,ROW(History!B3:B),History!B3:B=E1),
IF(G1=Settings!B2,ROW(History!C3:C),History!C3:C=G1)
)
)
[/vba]
Зарядил эту формулу в вашу таблицу, проверяйте (лист копии "Позиций" со старой формулой сохранил рядом).

Автор - Gustav
Дата добавления - 05.02.2021 в 17:18
tnt Дата: Пятница, 05.02.2021, 17:47 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Зарядил эту формулу в вашу таблицу, проверяйте (лист копии "Позиций" со старой формулой сохранил рядом).

Gustav, благодарю, в очередной раз выручаете.
Но возник есть вопрос, к примеру если в "History" есть такие строки:
Код

11/01/2021    TINKOFF    РОСТ    TSLA    Buy    3    $300.00    $900.00


Код

12/01/2021    TINKOFF    СПЕКУЛЯЦИЯ    TSLA    Buy    4    $289.00    $1,156.00

То в "Position" возникает ошибка, возможно ли один тикер (TSLA) разделять в разные категории?


Сообщение отредактировал tnt - Пятница, 05.02.2021, 17:48
 
Ответить
Сообщение
Зарядил эту формулу в вашу таблицу, проверяйте (лист копии "Позиций" со старой формулой сохранил рядом).

Gustav, благодарю, в очередной раз выручаете.
Но возник есть вопрос, к примеру если в "History" есть такие строки:
Код

11/01/2021    TINKOFF    РОСТ    TSLA    Buy    3    $300.00    $900.00


Код

12/01/2021    TINKOFF    СПЕКУЛЯЦИЯ    TSLA    Buy    4    $289.00    $1,156.00

То в "Position" возникает ошибка, возможно ли один тикер (TSLA) разделять в разные категории?

Автор - tnt
Дата добавления - 05.02.2021 в 17:47
tnt Дата: Пятница, 05.02.2021, 20:44 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Если к примеру в "History" у любого из Портфелей записана только 1 категория, к примеру портфель TINKOFF и только 1 строка СПЕКУЛЯЦИЯ, то выдает ошибку "Ссылка не существует". Почему так происходит, не пойму.


Сообщение отредактировал tnt - Суббота, 06.02.2021, 00:22
 
Ответить
СообщениеЕсли к примеру в "History" у любого из Портфелей записана только 1 категория, к примеру портфель TINKOFF и только 1 строка СПЕКУЛЯЦИЯ, то выдает ошибку "Ссылка не существует". Почему так происходит, не пойму.

Автор - tnt
Дата добавления - 05.02.2021 в 20:44
Gustav Дата: Суббота, 06.02.2021, 00:52 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Походу у Вас скрипт барахлит, но мне разбираться лениво. Предлагаю решение на двух формулах - соответственно для A3 (одна) и для B3 (протягиваемая вниз):
[vba]
Код
=SORT(UNIQUE(FILTER(History!D3:D,
IF(E1=Settings!A2,History!B3:B<>E1,History!B3:B=E1),
IF(G1=Settings!B2,History!C3:C<>G1,History!C3:C=G1),
History!D3:D<>"Cash"
)))

=IF(ISBLANK(A3),"", SUMIFS(History!$F$3:$F,
History!$D$3:$D, A3,
History!$B$3:$B, IF($E$1=Settings!$A$2,"<>","=") & $E$1,
History!$C$3:$C, IF($G$1=Settings!$B$2,"<>","=") & $G$1
))
[/vba]
Формулы вставлены в новый лист "Positions ON FORMULA". Сверяйте результаты со скриптом, смотрите что неправильно и почему. Почти уверен, что формулы дают правильный результат.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 06.02.2021, 13:37
 
Ответить
СообщениеПоходу у Вас скрипт барахлит, но мне разбираться лениво. Предлагаю решение на двух формулах - соответственно для A3 (одна) и для B3 (протягиваемая вниз):
[vba]
Код
=SORT(UNIQUE(FILTER(History!D3:D,
IF(E1=Settings!A2,History!B3:B<>E1,History!B3:B=E1),
IF(G1=Settings!B2,History!C3:C<>G1,History!C3:C=G1),
History!D3:D<>"Cash"
)))

=IF(ISBLANK(A3),"", SUMIFS(History!$F$3:$F,
History!$D$3:$D, A3,
History!$B$3:$B, IF($E$1=Settings!$A$2,"<>","=") & $E$1,
History!$C$3:$C, IF($G$1=Settings!$B$2,"<>","=") & $G$1
))
[/vba]
Формулы вставлены в новый лист "Positions ON FORMULA". Сверяйте результаты со скриптом, смотрите что неправильно и почему. Почти уверен, что формулы дают правильный результат.

Автор - Gustav
Дата добавления - 06.02.2021 в 00:52
tnt Дата: Суббота, 06.02.2021, 13:19 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Работает, а можно еще исключить фильтрацию значений, где Shares = 0 ?
 
Ответить
СообщениеРаботает, а можно еще исключить фильтрацию значений, где Shares = 0 ?

Автор - tnt
Дата добавления - 06.02.2021 в 13:19
Gustav Дата: Суббота, 06.02.2021, 13:35 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Соорудил новый скрипт. Поместил на новый лист "Positions NEW SCRIP". Вроде, чётко совпадает по результатам с листом "Positions ON FORMULA".
[vba]
Код
function MyPortfolio2(p_tickers, p_values) {
  
    var tickers = [].concat(p_tickers);
    var values  = [].concat(p_values);
  
    // отбор нужных данных из переданных массивов
    var allData = [];
    for(var i = 0; i < tickers.length; i++) {
        var t = tickers[i].toString();
        if(t && t != 'Cash') allData.push( {tick: t, val: Number(values[i])} );
    }
  
    // группировка по tickers с подсчетом сумм по values
    var groupBy = [];
    allData.reduce(function (res, value) {
        var key = value.tick.toUpperCase();
        if (!res[key]) {
            res[key] = {tick: key, val: 0};
            groupBy.push(res[key]);
        }
        res[key].val += value.val;
        return res;
    }, {});
  
    // сортировка по алфавиту (возрастающая) по полю tick (tickers)
    groupBy.sort(function(a,b) {return (a.tick > b.tick) - (b.tick > a.tick)});
  
    // формирование возвращаемого массива
    var total = [];
    groupBy.forEach(function(v) { if(v.val) total.push([v.tick, v.val]) });
    return total;
}
[/vba]
Поскольку новый скрипт сортирует по алфавиту, добавил функцию SORT и в первую формулу предыдущего поста. Чтобы удобнее было сравнивать, да и вообще в отсортированном виде как-то получше смотрится.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 06.02.2021, 13:46
 
Ответить
СообщениеСоорудил новый скрипт. Поместил на новый лист "Positions NEW SCRIP". Вроде, чётко совпадает по результатам с листом "Positions ON FORMULA".
[vba]
Код
function MyPortfolio2(p_tickers, p_values) {
  
    var tickers = [].concat(p_tickers);
    var values  = [].concat(p_values);
  
    // отбор нужных данных из переданных массивов
    var allData = [];
    for(var i = 0; i < tickers.length; i++) {
        var t = tickers[i].toString();
        if(t && t != 'Cash') allData.push( {tick: t, val: Number(values[i])} );
    }
  
    // группировка по tickers с подсчетом сумм по values
    var groupBy = [];
    allData.reduce(function (res, value) {
        var key = value.tick.toUpperCase();
        if (!res[key]) {
            res[key] = {tick: key, val: 0};
            groupBy.push(res[key]);
        }
        res[key].val += value.val;
        return res;
    }, {});
  
    // сортировка по алфавиту (возрастающая) по полю tick (tickers)
    groupBy.sort(function(a,b) {return (a.tick > b.tick) - (b.tick > a.tick)});
  
    // формирование возвращаемого массива
    var total = [];
    groupBy.forEach(function(v) { if(v.val) total.push([v.tick, v.val]) });
    return total;
}
[/vba]
Поскольку новый скрипт сортирует по алфавиту, добавил функцию SORT и в первую формулу предыдущего поста. Чтобы удобнее было сравнивать, да и вообще в отсортированном виде как-то получше смотрится.

Автор - Gustav
Дата добавления - 06.02.2021 в 13:35
Gustav Дата: Суббота, 06.02.2021, 13:53 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
можно еще исключить где Shares = 0 ?
В новом скрипте - можно: добавил if(v.val) в предпоследнюю строку.

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

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
можно еще исключить где Shares = 0 ?
В новом скрипте - можно: добавил if(v.val) в предпоследнюю строку.

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

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

Автор - Gustav
Дата добавления - 06.02.2021 в 13:53
tnt Дата: Суббота, 06.02.2021, 15:09 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Соорудил новый скрипт.


Благодарю! Теперь Всё чётко работает!
 
Ответить
Сообщение
Соорудил новый скрипт.


Благодарю! Теперь Всё чётко работает!

Автор - tnt
Дата добавления - 06.02.2021 в 15:09
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Отфильтровать список по значению из другой ячейки скриптом (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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