Сообщество - MS, Libreoffice & Google docs
Добавить пост

MS, Libreoffice & Google docs

711 постов 14 987 подписчиков

Популярные теги в сообществе:

Ищу специалиста. Формулы/автоматизация/построение google-таблиц

Всем привет!
Наша компания занимается прокатом (холодной арендой) спецтехники и оборудования. Сейчас пользуемся гугл-таблицами, но с ростом объёмов, растёт и количество информации, которую тяжело учитывать в "простеньких" таблицах созданных на коленке. Пробовали различные программы учёта проката, но есть много нюансов, которые не позволяют их полноценно использовать. На текущий момент есть потребность в создании "продвинутой" версии таблицы учёта с использованием макросов и сложных формул, но я не понимаю, кто этим занимается и как называется профессия, если это можно так назвать. Отсюда возникает вопрос - к кому обратиться с моей потребностью и есть-ли тут специалисты, готовые взяться за работу удалённо?
Находимся в Челябинске.

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно

Друзья, всем привет. Сегодня хотелось бы поговорить про непонятную и загадочную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Думаю, что каждый, кто хоть как-то работает со сводными таблицами, сталкивался с ней. Как обычно, я не расскажу ничего нового. Всё это уже есть так или иначе на просторах интернета. Я лишь поделюсь своим опытом и тем, как мне в своё время это функция очень помогла. Поехали.

Ссылка на файл (с уже прописанными формулами и одним листом, где можете попробовать прописать самостоятельно) - https://disk.yandex.ru/i/XJNiy7WI2rrMqQ

Начало.

Обычно первое знакомство происходит примерно так. Вы построили сводную таблицу, потом возникает необходимость сослаться на какую-нибудь ячейку внутри этой сводной, но вместо любимых и ламповых ссылок типа В4 мы получаем вот это:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

Ступор, небольшое замешательство, осознание, что чего-то пошло не так. Самые смелые заканчивают ввод формулы, видят нормальное значение, радуются, копируют формулу, после чего радость заканчивается. Потом, скорее всего, поиски в интернете "как избавиться от ПОЛУЧИТЬ.ДАННЫЕ...". Про то, как избавиться, мы ещё поговорим в самом конце. А пока...

В чём сила этой функции, брат?

Если вы будете смотреть различные видео по этой функции, почти во всех из них будут упоминаться диаграммы. Спорить не буду, у сводных диаграмм есть некоторые ограничения, но я, если честно, решил этот момент пропустить, так как разными путями эти ограничения можно обойти (если конкретно вам эта функция помогает именно при построении диаграмм, напишите в комментариях). Речь же пойдёт про другое, а именно, про перенос данных из сводной таблицы в обычный отчёт (таблицу). Вот тут, как мне кажется, функция раскрывает весь свой потенциал. Есть у нас данные, мы построили на основе этих данных сводную:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

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

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

Давайте сейчас определим сложности, с которыми мы столкнёмся, и которые не позволят нам банальным копированием-вставкой или прямой ссылкой заполнить наш отчёт:

  1. Клиенты продавали не все наименования. Но, в теории, всё это у них может быть.

  2. Порядок клиентов в сводной таблице и в отчёте разный ("потому что" ©).

  3. Отчёт сразу за год, а у нас пока данные только до ноября. Добавлять формулы потом отдельно на декабрь не очень хочется. Хочется в начале года прописать формулу, потом сводную обновлять и радоваться жизни.

  4. Почему не СУММЕСЛИМН? Потому что структура отчёта не позволяет суммировать продажи по наименованиям внутри каждого клиента. Или городить какую-нибудь формулу массива (не пробовал, но может и получится).

  5. Можно ещё немного пофантазировать и предположить, что могут появиться новые клиенты и новые наименования. И решение должно это учитывать (добавил клиента с наименованиями, скопировал формулу, гордый собой пошёл пить чай/кофе).

И вот тут как раз на сцену выходит она - ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData).

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

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

Первый аргумент - поле из сводной, по которому мы производим вычисления. Далее - ЛЮБАЯ ячейка из сводной таблицы (обычно берут верхнюю левую). А потом идут пары: в каком поле что нужно найти.

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

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1)

Исходник 2 - лист, на котором находится сама сводная таблица.

В ячейке хотим видеть значение из поля "Сумма, руб" нашей сводной. G1 - ссылка на ячейку сводной. А дальше те самые пары. В поле "Наименование" ищем значение из ячейки А3 (1), в "Клиент" ищем А2 (2), в "Месяцы(даты)" ищем В1 (3). Порядок пар роли не играет.

ВАЖНО! Названия полей (наименование, клиент, месяцы(даты)), тоже можно сделать в виде ссылок на ячейки, если таковые есть. А вот название поля данных, в котором происходит расчёт (сумма, руб) обязательно должно быть указано текстом. В любом случае, названия должны совпадать с теми, которые указаны в сводной таблице.

Если сейчас скопировать формулу на все месяцы, то в декабре будет ошибка #ССЫЛКА, так как нет такого элемента в сводной таблице. Чтобы убрать это непотребство, просто добавляем функцию ЕСЛИОШИБКА:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

=ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1);"")

Да, решение далеко от идеального. Хотя бы потому, что для клиентов придётся писать свою формулу, а для наименований свою. Скопировать всё разом на всю таблицу тоже не получится, потому что нужно будет менять ссылку с названием клиента. Но это всё делается за минуту. А преимущества такого подхода очевидны:

  1. Если в нашей сводной что-то будет смещено, то формула не сломается, в отличие от прямой ссылки.

  2. Когда будут данные за декабрь, мы их добавляем в нашу "умную" таблицу, обновляем сводную и готово.

  3. Если появится новый клиент или наименование, то нужно будет добавить новые строки в отчёт и скопировать формулу.

Недостатки у такого подхода, конечно, тоже имеются:

  1. Если фильтровать сводную или полностью перелопатить её структуру, то значения в формуле будут пересчитываться в соответствии с тем, что сейчас отображается в сводной. Таким образом, например, если поставим фильтр в сводной таблице на какие-то определённые наименования, а потом забудем фильтр очистить, то в отчёте суммы по этим наименованиям потеряем (ячейки будет пустыми, ошибок не будет, ведь мы приправили всё ЕСЛИОШИБКОЙ).

  2. Если отчёт и сводная находятся в разных книгах, то формула будет работать только тогда, когда обе книги открыты одновременно (не относится к ситуации, когда обе открыли, а потом книгу со сводной закрыли).

Заключение.

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

Как обычно, спасибо всем, кто потратил своё драгоценное время на чтение данной статьи. Надеюсь, было полезно. И да, если уже пользуетесь этой функцией, поделитесь в комментариях, какие задачи она вам помогает решать. Наверняка есть что-то такое, чего я о ней не знаю (никогда не было стыдно признаться в том, что я чего-то не знаю). Или если при использовании данной функции вы сталкивались с какими-то критичными трудностями. Да пребудет с нами сила ИКСэль.

P.S.

Алё, а отключить-то как?! На всякий случай напишу, как отключить автоматическое создание этой функции, когда ссылаемся на ячейки сводной (большинству она вот совсем не нужна). Всё просто:

  1. Выбираем любую ячейку сводной.

  2. Вкладка Анализ - группа Сводная таблица - раскрываем Параметры - снимаем галочку Создать GetPivotData.

  3. Profit!!11

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - что это вообще такое и зачем нужно Таблица, Microsoft Excel, Microsoft office, Длиннопост

Больше вас это богомерзкое чудовище не побеспокоит. А если нужна будет, можете вводить её как обычную функцию.

Показать полностью 6

Создание собственных наборов, срезов встроенными средствами Excel

Коллеги, добрый день.
Я не уверен, что я корректно задаю вопрос. Именно от собственной неуверенности и пытаюсь что-то спросить на тему.
Вопрос такой...

Я подключил большую таблицу из базы.
Она самодостаточна, т.е. в ней есть все поля, которые мне нужны в качестве срезов. Подключил я её через power Query, импортнул в модель данных. На её основе строю сводные таблицы и графики. Пока всё норм.

Но в какой-то момент своей жизни я видел, что можно создавать собственные наборы для формирования срезов. Причём, это делалось не средствами Power Pivot, а средствами на панели Excel: Анализ сводной диаграммы -> блок "Вычисления" ->Поля, элементы и наборы данных.

Я попытался погуглить эту тему, но у меня что-то либо очень сложное, либо на каких-то узких примерах попалось.

Пожалуйста, подскажите, где набраться мудрости?

Спасибо.

Вы заканчивали онлайн-магистратуру? Расскажите, пожалуйста, о своем опыте!

Дистанционное обучение сегодня круче, чем когда-либо. Даже магистратуру теперь можно закончить из дома, да еще и с дипломом государственного образца.

Если вы получали высшее образование онлайн, поделитесь, пожалуйста, своим опытом. Нам интересно ваше мнение.

Google таблицы элементы управления формы

Вопрос. Есть в богомерзких гугл таблицах что-то по типу элементов управления формы, как в православном Экселе?

Нашел там только хуево сделанный выпадающий список.

Мне надо что-то вроде

Google таблицы элементы управления формы Google docs, Microsoft Excel, Гифка, Мат

Я полагаю какими-то расширениями можно сделать мб? В базе этого нет?

Выгрузка из CVS пошла не так. Есть идеи, где косяк? [Решено]

Так случилось, что сменил я марку смартфона с самсунга на рилми, сбыл самсунг на авито и только после этого обнаружил, что все мои контакты, любовно собираемые последние 15 лет, синхронизировались не через гугл, а через самсунг-аккаунт (да, сам идиот).

Ну, думаю, не беда, облака ж они и есть облака - запросил у samsung privasy выгрузку всего содержимого аккаунта (38 томов архива по 500мб, яебал...), нашёл там папку Contact, а в ней энное количество файлов CVS, в каждом по 200 контактов.

А дальше пошло дерьмо. Я попытался загрузить эти файлы в Google Контакты и оказалось, что имена контактов он видит нормально, а собственно номера телефонов - ну, короче как-то так)

Выгрузка из CVS пошла не так. Есть идеи, где косяк? [Решено] Microsoft Excel, Cvs, База данных, Компьютерная помощь, Длиннопост

То есть выкинул номер в заметки. Значит, что-то не так со столбцами. Поэтому я пошёл в excel 2019, создал пустую книгу и через вкладку "Данные" - "Из текстового/CSV..." загрузил файл туда.

Выгрузка из CVS пошла не так. Есть идеи, где косяк? [Решено] Microsoft Excel, Cvs, База данных, Компьютерная помощь, Длиннопост

Получил следующую картину:

Выгрузка из CVS пошла не так. Есть идеи, где косяк? [Решено] Microsoft Excel, Cvs, База данных, Компьютерная помощь, Длиннопост

И поближе:

Выгрузка из CVS пошла не так. Есть идеи, где косяк? [Решено] Microsoft Excel, Cvs, База данных, Компьютерная помощь, Длиннопост

Т.е. номера телефонов существуют, они отображены нормально, они единообразно размещены в маске "data1":"блаблабла", но в столбце эта информация соседствует с кучей технического барахла, которого там явно не должно было быть.

В связи с чем два вопроса:

A. Что я сделал не так и была ли возможность открыть этот файл нормально, чтобы столбцы распределились адекватно и номера выделились в столбец сами?

B. Что я могу сделать, помимо ручной работы, чтобы выкинуть из столбца всё лишнее, кроме маски "data1":"блаблабла"?

Там всё-таки семь файлов по 200 строк, вручную как-то ну вообще совсем не хочется...

UPD. Получившийся файл, в котором слегка изменены сами номера и оставлены для образца только несколько строк: https://dropmefiles.com/hWzxZ

Показать полностью 4

Сравнение двух столбцов excel

Добрый день! Гуглеж ничем не помог, подозреваю вопрос не верно сформулирвал в поиске.

Есть столб А, столб В. В первом порядка 30 уникальных значений, во второй на несколько порядков больше + есть дубли.Пример:

Сравнение двух столбцов excel Microsoft Excel, Вопрос

Как мне найти дубли выделенные желтым? Т.е. при совпадении в двух столбцах
Заранее благодарю

Год +1

Привет ценителям экселя!

Есть книга которая состоит из кучи листов с которых сводится все данные в один, все работает и ошибок нет, но начинаются даты с 01.01.2023 и заканчиваются 31.01.2024.

в первой ячейке дата формируется =ДАТА(Установки!$B$2;1;1) - это у нас 01.01.2023, а последняя =B404+1 и это у нас 31.01.2024. Такая структура на всех листах идет.

Как добавить +1 год (стать должно 01.01.2024 и по 31.01.2025) ? В идеале конечно на все листы (их тут под 100)

Пробовал через костыль сделать: =ДАТА(Установки!$B$2;12;1) ну мол пусть так начинается, но поехала строка данные на.

Визуально оно вот так

Год +1 Помощь, Microsoft Excel

Платформы для онлайн-обучения: что вы о них думаете? Поделитесь мнением!

Онлайн-курсов становится все больше, и нам интересно собрать статистику. Пожалуйста, пройдите небольшой опрос и поделитесь своим мнением!

Как вставлять фотографии в ексель?

Всем привет,
Есть задача:
Есть файл ексель, где в колонке "A" прописаны номера артикулов.
Есть папка на диске., "C:\Фотобанк", где располагаются фотографии, названия которых совпадают с номерами артикулов из колонки "A".
Есть колонка "K", ячейка: высота 100пикселей, ширина 140пикселей. куда нужно вставить эти фотографии, ссылаясь на колонку "А". Фотографии должны при этом сохранять своё соотношение сторон и качество, помещаясь при этом в ячейку 140*100
Как это лучше сделать?

UPD: Если кому-нибудь нужно данное решение, обращайтесь!

Отличная работа, все прочитано!