Здравствуйте! Помогите найти решение, как можно сделать фильтрацию нужного диапазона значений. Имею таблицу: 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]
Здравствуйте! Помогите найти решение, как можно сделать фильтрацию нужного диапазона значений. Имею таблицу: 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 }
Gustav, Благодарю! Думал, что уже без редактирования скрипта не получится.
Но возникла другая проблема, и я не понимаю по какой логике. Теперь если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает. С чем это связано?
Gustav, Благодарю! Думал, что уже без редактирования скрипта не получится.
Но возникла другая проблема, и я не понимаю по какой логике. Теперь если в листе "Settings" вписать вместо числовых значений в списке буквенные, например дать имена брокерам, то формула при выборе "ВСЕ" не работает. С чем это связано?tnt
[/vba] Связано это с тем, что второй аргумент функции IF должен быть набором либо логических значений, либо чисел (потому что любое число, не равное 0 - это TRUE, а 0 - это FALSE). Поэтому пока в первом варианте там были числа хватало просто ссылки History!B3:B1000, в ячейках которой были числа (>0 = TRUE). Для текста же такая автоматическая замена не работает. Поэтому надо каким-то образом "превратить" значения этих ячеек либо в положительные числа, либо в TRUE. Я использовал функцию ROW для превращения в числа. Вместо ROW можно было бы использовать, например, ISTEXT или LEN. Наверняка можно еще какие-нибудь вспомнить...
Кстати, вот еще забавный вариант, который, наверное, вообще надо было предложить раньше всех [vba]
[/vba] Связано это с тем, что второй аргумент функции IF должен быть набором либо логических значений, либо чисел (потому что любое число, не равное 0 - это TRUE, а 0 - это FALSE). Поэтому пока в первом варианте там были числа хватало просто ссылки History!B3:B1000, в ячейках которой были числа (>0 = TRUE). Для текста же такая автоматическая замена не работает. Поэтому надо каким-то образом "превратить" значения этих ячеек либо в положительные числа, либо в TRUE. Я использовал функцию ROW для превращения в числа. Вместо ROW можно было бы использовать, например, ISTEXT или LEN. Наверняка можно еще какие-нибудь вспомнить...
Кстати, вот еще забавный вариант, который, наверное, вообще надо было предложить раньше всех [vba]
А как можно фильтрацию по значениям разложить еще на категории? Например сейчас в таблице можно выбирать "Портфель" и сделать фильтрацию, а как добавить к каждому портфелю еще и категории? Только через усложнение формулы через ЕСЛИ?
На листе "History" создается запись (строка), которая прикрепляется к определенному портфелю и категории этого портфеля. Список Портфелей и категорий прописан на листе "Settings". На листе "Positions" должна происходить фильтрация по выбранному портфелю и его категории. Как можно это осуществить?
А как можно фильтрацию по значениям разложить еще на категории? Например сейчас в таблице можно выбирать "Портфель" и сделать фильтрацию, а как добавить к каждому портфелю еще и категории? Только через усложнение формулы через ЕСЛИ?
На листе "History" создается запись (строка), которая прикрепляется к определенному портфелю и категории этого портфеля. Список Портфелей и категорий прописан на листе "Settings". На листе "Positions" должна происходить фильтрация по выбранному портфелю и его категории. Как можно это осуществить?tnt
Если к примеру в "History" у любого из Портфелей записана только 1 категория, к примеру портфель TINKOFF и только 1 строка СПЕКУЛЯЦИЯ, то выдает ошибку "Ссылка не существует". Почему так происходит, не пойму.
Если к примеру в "History" у любого из Портфелей записана только 1 категория, к примеру портфель TINKOFF и только 1 строка СПЕКУЛЯЦИЯ, то выдает ошибку "Ссылка не существует". Почему так происходит, не пойму.tnt
Сообщение отредактировал tnt - Суббота, 06.02.2021, 00:22
Походу у Вас скрипт барахлит, но мне разбираться лениво. Предлагаю решение на двух формулах - соответственно для A3 (одна) и для B3 (протягиваемая вниз): [vba]
[/vba] Формулы вставлены в новый лист "Positions ON FORMULA". Сверяйте результаты со скриптом, смотрите что неправильно и почему. Почти уверен, что формулы дают правильный результат.
Походу у Вас скрипт барахлит, но мне разбираться лениво. Предлагаю решение на двух формулах - соответственно для A3 (одна) и для B3 (протягиваемая вниз): [vba]
[/vba] Формулы вставлены в новый лист "Positions ON FORMULA". Сверяйте результаты со скриптом, смотрите что неправильно и почему. Почти уверен, что формулы дают правильный результат.Gustav
Соорудил новый скрипт. Поместил на новый лист "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 и в первую формулу предыдущего поста. Чтобы удобнее было сравнивать, да и вообще в отсортированном виде как-то получше смотрится.
Соорудил новый скрипт. Поместил на новый лист "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
В новом скрипте - можно: добавил if(v.val) в предпоследнюю строку.
В формулах в такой редакции, как сейчас - нельзя. Нужно сильно курочить первую формулу, потому что фактически нужно будет предварительно сосчитать и проверить суммы перед тем как сформировать уникальный список тикеров. Т.е. фактически выполнить двойную работу.
Но можно кардинально переделать формулы с использованием функции QUERY и тогда получится элегантно исключить строки с нулевыми суммами. Мне сейчас опять лениво этим заниматься. Может быть, чуть позже. Ну, или какой-нибудь другой читатель разомнёт свои навыки и подсобит.
В новом скрипте - можно: добавил if(v.val) в предпоследнюю строку.
В формулах в такой редакции, как сейчас - нельзя. Нужно сильно курочить первую формулу, потому что фактически нужно будет предварительно сосчитать и проверить суммы перед тем как сформировать уникальный список тикеров. Т.е. фактически выполнить двойную работу.
Но можно кардинально переделать формулы с использованием функции QUERY и тогда получится элегантно исключить строки с нулевыми суммами. Мне сейчас опять лениво этим заниматься. Может быть, чуть позже. Ну, или какой-нибудь другой читатель разомнёт свои навыки и подсобит.Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
