Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Визуализация данных – какой продукт выбрать?, Прошу дать совет общего характера, в каком направлении решать задачу
 
Sertg, Ваш скрин-шот впечатляет! Спасибо за ссылку на статью. Буду изучать.
Визуализация данных – какой продукт выбрать?, Прошу дать совет общего характера, в каком направлении решать задачу
 

Добрый день!

Есть задача анализа и визуализации данных.

Данные – сведения о бронировании автомобилей. Они выгружаются с сайта. На листе «Данные» это столбцы А-Р. Три правых синих столбца – дополнительные, вычисляются по формулам, которые «протягиваются» уже потом – после выгрузки данных.

Анализ данных – на листе «Анализ». Реализован с помощью функций СУММЕСЛИМН и СЧЁТЕСЛИМН с учетом Периода (ячейки В3-М3) и Статуса (ячейка В2).

Задача получить такой анализ для каждой пары столбцов «Страна» (столбец N) и «Место полученияСтрана» (столбец R).

В принципе, несложно сделать сводную таблицу, как показано на листе «Свод». Однако реальные данные содержать несколько тысяч строк и сводная получается очень большая.

Хотелось бы показывать не все пары, а только первые (максимальные) 10. Причем по каждому показателю (Комиссия, АрендоДни, Цена и др.) будет своя 10-ка максимальных. Стандартный фильтр "первые 10" собирает 10 максимальных броней – но не 10 максимальных сумм "по парам".

Сейчас все (кроме сводной таблицы) реализовано в ГуглТаблицах. Это связано с пожеланием шефа, что бы отчет можно было просматривать через Интернет. Шеф использует Mac и не очень приветствует MS Excel…

Собственно, вопрос:

Какой продукт вы можете посоветовать для этой задачи? Для создания чего-то типа «дашборд»…

Из-за недостатка опыта не знаю, куда направить усилия: формулы в Гугл Таблицах (инструменты Гугл предпочтительней – шефу удобнее пользоваться через web), Google Data Studio, Excel, Power BI или что-то другое?

Примечание:

Конечно, мне хотелось бы наиболее простой инструмент (на сегодняшний день знаком только с Excel и Google Tabs), чтобы уменьшить время на его освоение и показать шефу результат в минимальные сроки.

Заранее спасибо.

PS

Надеюсь, что не нарушаю Правила, разместив вопрос об этом и на другом форуме

http://www.excelworld.ru/forum/23-43984-1

Изменено: avbook - 31 янв 2020 21:59:21 (Забыл приложить файл.)
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
Sceptic написал:
У меня такой вариант сработал
Спасибо !!! "Заработало !"
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
Sceptic написал:
пересчет стоит ручной?
Верно. Я копировал из рабочего файла. Там ручной пересчет. Сам пересчет занимает секунд 30-40, поэтому я поставил ручной.
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
vikttur Спасибо. Я видел макросы на других форумах. Но пока количество ячеек не так велико. Мне  проще через Del (это помогает), чем тратить время на освоение макросов (я пока с ними "на вы").
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
vikttur написал:
Выделить и нажать Delete
Т.е. вручную?
Дело в том, что в реальном файле такие ячейки "разбросаны" по таблице. И это занимает слишком много времени...
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 

Добрый день!

Ситуация следующая: есть Гугл Таблица, из нее при помощи запроса Query заполняется промежуточная таблица Excel, далее Copy-Paste данные переносятся в рабочий файл. Так делается ежемесячно – в рабочем файле хранятся данные за все периоды.

Я обнаружил, что в рабочем файле есть ячейки, которые внешне кажутся пустыми, но на самом деле содержат что-то. В моем примере я выделил такие ячейки желтой заливкой. Эти «пустые» ячейки влияют на работу сводных таблиц и еще, наверное, увеличивают объем файла. В реальном файле около 10,000 строк.

Вопрос: как очистить такие «пустые» ячейки? Есть ли относительно простой способ (без макросов), который можно было бы повторять каждый месяц, и который не изменял бы данные в рабочем файле.

Или может надо что-то докурить в запросе Query?

Заранее спасибо.

Быстрый поиск уникальных пар значений
 
Цитата
TheBestOfTheBest написал:
Может сводную формировать через Power Query?
Не знаком с Power Query, к сожалению... А можно увидеть пример?
Быстрый поиск уникальных пар значений
 
vikttur, все исправил.  Спасибо за замечание. Учту на будущее.
Быстрый поиск уникальных пар значений
 
Цитата
БМВ написал:
В таблице еще много расчетов (в столбцах)  и на SUMIF(S)....
Добрый день всем! Как автор таблицы могу сказать, что из 5500 строк собирается около 200 пар значений. Далее для каждой пары по формуле СУММЕСЛИМН идет расчет 2-х показателей за каждый месяц за 4 года. т.е.  около 20,000 ячеек. И еще на  других листах есть около 8000 ячеек с формулами СУММЕСЛИМН.
Цитата
БМВ написал:
при этом есть что считать или нет - было отдано на откуп многочисленным суммам.
"Пустых расчетов" немного - около 20 строк. Формула определения пар "протянута" вниз с запасом, чтобы пользователь, который вносит данные "не беспокоился" о появлении новых пар. Иначе пришлось бы каждый раз проверять, все ли пары вошли в расчет. Ну и конечно, Эксель не знает, для какой пары в каком месяце нет оборотов. Это еще одни источник "пустых" суммирований.
Изменено: avbook - 16 июл 2018 14:08:28
Быстрый поиск уникальных пар значений
 
Цитата
gling написал:
Вариант формулами с доп столбцами которые можно скрыть.
БОЛЬШОЕ спасибо gling за решение в сообщении #8 ! Немного доработал под себя и все работает! Сокращение времени почти в 8 (восемь!) раз. Обсчет всего файла занимает примерно 40 сек. Вот готовое решение.
Быстрый поиск уникальных пар значений
 
Цитата
БМВ написал:
все верно, порой пара столбцов делает расчет в разы проще
БМВ Вы уже писали это где-то. Поэтому я и обратился к Форуму.  Когда сам пришел в тупик - нужен свежий взгляд и более опытный.
Скрытый лист - отличная идея!
Быстрый поиск уникальных пар значений
 
gling Вы используете ссылку на весь столбец типа А:А, можно наверное для сокращения времени сделать "закрытый" диапазон типа $A$1:$A$545 ?
PS Уже сегодня не смогу испытать на своем "живом" файле...За целый день уже "глаза" в кучку. Напишу Вам позже, что получилось в этой теме.
Спасибо!
Быстрый поиск уникальных пар значений
 
БМВ, еще раз Вам большое спасибо. Но я точно к ночи уже торможу немного... Вы поставили фильтр "неравно пустому значению" ? В сообщении #4
Быстрый поиск уникальных пар значений
 
Понятно. Но "старые" все равно будут там "висеть"?
Быстрый поиск уникальных пар значений
 
БМВ спасибо за сводную таблицу. Я теперь вспомнил, почему я не стал ее использовать. При появлении новых элементов, их надо вручную добавлять в фильтр сводной таблицы. А при их удалении из таблицы источника, они все равно там "висят"...
Возможно, я не очень хорошо умею пользоваться сводными таблицами...
Быстрый поиск уникальных пар значений
 

Добрый день!

Прошу уважаемое сообщество помочь. Необходимо находить уникальные пары значений. На одном листе пара Контрагент-СтатьяБюджета, на другом Контрагент-ГруппаСтатейБюджета.

Предложенное решение в рабочем файле с 5500 строк на листе «Реестр» требует 8 минут на обсчет. Как повысить быстродействие файла? Надо сохранить структуру файла, т.к. в реальной таблице на каждом из листов справа идут вычисления, которые здесь не показаны.

Заранее спасибо.

Поиск по двум таблицам по разным условиям, без макросов
 
Всем - всем Большое спасибо!
Наверное можно (не против Правил?) поделиться формулами, которые мне в другом Форуме написали

Формула массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Исключения;ПОИСКПОЗ(C3&B3;I$3:I$5&J$3:J$5;);3);ВПР(C3;F$3:G$7;2;))

Обычная формула
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3&B3=I$3:I$5&J$3:J$5);K$3:K$5);ВПР(C3;F$3:G$7;2;))
Поиск по двум таблицам по разным условиям, без макросов
 
Добрый день!

Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия  этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.

Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".

У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).

Понимаю, что быстродействие - это отдельная задача, но все же прошу уважаемое сообщество посмотреть, может кто-то предложит более эффективный способ. В файле - 2 листа: описание задачи и мое решение.

Заранее большое спасибо.

Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Цитата
БМВ написал:
CTR+SHIFT+ENTER
Вы правы! Это я что-то к вечеру торможу... Эта формула тоже работает! Ее надо вводить, как формулу массива.

Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
БМВ, большое спасибо! Конечно, интересно и полезно. И английский опять же подтянуть. :)
PS Повторюсь, что первоначально настраивал таблицы в Excel, т.к. знаю его много больше, чем GS, и работу надо было сделать в срок, а уже потом переносил... Не в качестве оправдания, но, как история вопроса...
PPS И еще раз спасибо за ссылку.
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
БМВ, Вы говорите вот об этой формуле ?
Код
=СУММПРОИЗВ(СУММЕСЛИМН(B3:B209;A3:A209;A224;E3:E209;Список))
Так с нее все и началось. Я использовал именованный диапазон "Список", чтобы не писать цепочку формул СУММЕСЛИМН() + СУММЕСЛИМН() + СУММЕСЛИМН() +... А потом, когда потребовалось перенести файл в Гугл, возникли проблемы... Пришлось задачу решать, стал спрашивать совета у всех...
Пока еще не так глубоко владею Excel, чтобы понимать причины, почему не работает... и, наоборот,  как работает... :)
Изменено: avbook - 8 июл 2018 20:31:05
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Цитата
БМВ написал:
не понятно только при чем тут SUMPRODUCT
СУММПРОИЗВ и SUMPRODUCT - это же одно и тоже - если не ошибаюсь... Так вот именно с ней у меня были вопросы...
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Спасибо всем, кто откликнулся!
Хочу поделиться своими результатами исследования на тему "Функция СУММПРОИЗВ в  Excel и GS.
Если кратко, есть формулы, которые работают в обеих системах, есть, которые только в одной. Примеры - в приложенном файле. Надеюсь, кому-то это поможет.
С уважением,
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Спасибо Irregular Expression за пояснения. Я интуитивно понимал, что это разные вещи. Теперь буду знать чуть-чуть больше. :)
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Спасибо пользователю БМВ за подсказку. На самом деле была ошибка в моем файле. В Экселе она обозначается как #ЗНАЧ!, а в Гугл Таблицах было написано, что синтаксическая. Вот это и сбило меня с толку. Нет, пока я далек от поклонника Гугл Таблиц!

Цитата
БМВ написал:
там еще есть ряд ограничений, которые могут сделать работу от неудобной до невыносимой.
Конечно, наша беседа не должна быть лекцией, но все же буду Вам очень признателен, если объясните подробнее, о каких ограничениях Вы говорите. Заранее спасибо.
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Конечно, для совместной работы важно режим реального времени. У меня был опыт совместной работы в файле Excel. Файл лежал на сервере в офисе. Нормально все работали. Да, надо чаще нажимать "Сохранить", но это не беда.

Что касается русской или английской версии. Когда я пытаюсь в Гугл Таблице  ввести название функции на английском IF, таблица сама переводит это на русский ECЛИ. Значит, собака в другом месте зарыта...

3й момент. Сейчас еще раз проверил вот эту формулу - таблица выдает ошибку. См. скрин-шот.
=СУММПРОИЗВ((A1:A2922=A6005)*(E1:E2922=B6005);B1:B2922)

В чем же дело? БМВ, может быть, Вы сможете подсказать?
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
БМВ, спасибо и Вам за ответ и за ссылку - почитаю на английском...
Интересно, что для Вас п.1   -  in real-time - это достоинство... Может быть, если речь идет о заполнении данных, отчетов. Но, мое мнение, не для создания и творчества. Нет возможности выйти без сохранения. Но, может быть, это лично моя манера работы...
Функции, которые я указал в первом сообщении - точно не работают - проверял не один раз...
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 

Спасибо Ігор Гончаренко за отзыв.

Читать HELP к самим Гугл Таблицам – дело неблагодарное, да и нет там толком информации, как в любой «помощи»…

Мне пока однозначно кажется, что у Гугл Таблиц возможностей меньше. Буду рад, если Вы меня поправите. Во всяком случае, в плане удобства работы: нет (или я их не нашел), к примеру, таких инструментов, как трассировка формул, окошко «аргументы функции» (когда нажимаешь на Fx) или горячие клавиши типа CTRL+[… и др. ... И еще один момент: файл сразу(!) записывает все изменения, поэтому вернуться к началу, когда тебя понесло «по волнам вдохновения» в изменении формул или данных, практически невозможно (разве если только нажимать CTRL+Z тысячу раз… Короче, я бы не стал делать даже простенькую финансовую модель в этом инструменте. Неудобно!

К сожалению, не нашел в сети ресурса (искал только среди русскоязычных), который был бы подробно посвящен Гугл Таблицам. Только перепечатка раздела помощи…

Именно поэтому надеялся, что на этом ресурсе найдется кто-то, кто плотно работал именно с Гугл Таблицами. Чтобы понять с какими задачами к ним лучше не приступать, чтобы не попасть впросак перед заказчиком.

Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
Уважаемые коллеги.
Работал ли кто-то из Вас с Гугл Таблицами? Все ли функции Excel там работают?

Делал работу для заказчика. Все настраивал в Excel, а потом, когда по требованию заказчика перенес на Гугл, обнаружил, что некоторые формулы считают неправильно или выдают синтаксическую ошибку. В частности, речь идет о формулах
Код
=СУММПРОИЗВ((A1:A2922=A6005)*(E1:E2922=B6005);B1:B2922)
=+СУММПРОИЗВ(СУММЕСЛИМН(Реестр!$I$1:$I$111;Реестр!$W$1:$W$111;"01-18";Реестр!$E$1:$E$111;"Валюта"))

Собственно, вопрос я бы поставил шире: какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?

Также буду благодарен за ссылки на материалы по Гугл Таблицам.
Спасибо.
Страницы: 1 2 След.
Наверх