Помогите с макросом
Здравствуйте, дано: три ячейки с выпадающими списками, нужен макрос выбирающий первое значение в каждом
Здравствуйте, дано: три ячейки с выпадающими списками, нужен макрос выбирающий первое значение в каждом
Друзья, всем привет. Сегодня хотелось бы поговорить про непонятную и загадочную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Думаю, что каждый, кто хоть как-то работает со сводными таблицами, сталкивался с ней. Как обычно, я не расскажу ничего нового. Всё это уже есть так или иначе на просторах интернета. Я лишь поделюсь своим опытом и тем, как мне в своё время это функция очень помогла. Поехали.
Ссылка на файл (с уже прописанными формулами и одним листом, где можете попробовать прописать самостоятельно) - https://disk.yandex.ru/i/XJNiy7WI2rrMqQ
Обычно первое знакомство происходит примерно так. Вы построили сводную таблицу, потом возникает необходимость сослаться на какую-нибудь ячейку внутри этой сводной, но вместо любимых и ламповых ссылок типа В4 мы получаем вот это:
Ступор, небольшое замешательство, осознание, что чего-то пошло не так. Самые смелые заканчивают ввод формулы, видят нормальное значение, радуются, копируют формулу, после чего радость заканчивается. Потом, скорее всего, поиски в интернете "как избавиться от ПОЛУЧИТЬ.ДАННЫЕ...". Про то, как избавиться, мы ещё поговорим в самом конце. А пока...
Если вы будете смотреть различные видео по этой функции, почти во всех из них будут упоминаться диаграммы. Спорить не буду, у сводных диаграмм есть некоторые ограничения, но я, если честно, решил этот момент пропустить, так как разными путями эти ограничения можно обойти (если конкретно вам эта функция помогает именно при построении диаграмм, напишите в комментариях). Речь же пойдёт про другое, а именно, про перенос данных из сводной таблицы в обычный отчёт (таблицу). Вот тут, как мне кажется, функция раскрывает весь свой потенциал. Есть у нас данные, мы построили на основе этих данных сводную:
Предположим, что таблица постепенно наполняется. То есть сейчас есть данные до ноября, но потом будут и за декабрь. Источник лучше преобразовать в "умную" таблицу, так потом будет чуть проще обновлять сводную. Данные эти нам потом необходимо перенести в отчёт установленной формы:
Давайте сейчас определим сложности, с которыми мы столкнёмся, и которые не позволят нам банальным копированием-вставкой или прямой ссылкой заполнить наш отчёт:
Клиенты продавали не все наименования. Но, в теории, всё это у них может быть.
Порядок клиентов в сводной таблице и в отчёте разный ("потому что" ©).
Отчёт сразу за год, а у нас пока данные только до ноября. Добавлять формулы потом отдельно на декабрь не очень хочется. Хочется в начале года прописать формулу, потом сводную обновлять и радоваться жизни.
Почему не СУММЕСЛИМН? Потому что структура отчёта не позволяет суммировать продажи по наименованиям внутри каждого клиента. Или городить какую-нибудь формулу массива (не пробовал, но может и получится).
Можно ещё немного пофантазировать и предположить, что могут появиться новые клиенты и новые наименования. И решение должно это учитывать (добавил клиента с наименованиями, скопировал формулу, гордый собой пошёл пить чай/кофе).
И вот тут как раз на сцену выходит она - ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData).
Не смотря на всю свою несуразность и загадочность, начиная с названия и заканчивая синтаксисом, функция довольно простая:
Первый аргумент - поле из сводной, по которому мы производим вычисления. Далее - ЛЮБАЯ ячейка из сводной таблицы (обычно берут верхнюю левую). А потом идут пары: в каком поле что нужно найти.
Первоначально нужные элементы указываются в виде текста. Вот тут и начинается самое интересное. Ведь вместо текста можно указывать ссылки на ячейки (учитывая все закрепления, конечно же). То есть если возвращаться к нашему отчёту, формулу для наименований можно прописать следующим образом (показана формула для ячейки В3):
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";
'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1)
Исходник 2 - лист, на котором находится сама сводная таблица.
В ячейке хотим видеть значение из поля "Сумма, руб" нашей сводной. G1 - ссылка на ячейку сводной. А дальше те самые пары. В поле "Наименование" ищем значение из ячейки А3 (1), в "Клиент" ищем А2 (2), в "Месяцы(даты)" ищем В1 (3). Порядок пар роли не играет.
ВАЖНО! Названия полей (наименование, клиент, месяцы(даты)), тоже можно сделать в виде ссылок на ячейки, если таковые есть. А вот название поля данных, в котором происходит расчёт (сумма, руб) обязательно должно быть указано текстом. В любом случае, названия должны совпадать с теми, которые указаны в сводной таблице.
Если сейчас скопировать формулу на все месяцы, то в декабре будет ошибка #ССЫЛКА, так как нет такого элемента в сводной таблице. Чтобы убрать это непотребство, просто добавляем функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";
'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1);"")
Да, решение далеко от идеального. Хотя бы потому, что для клиентов придётся писать свою формулу, а для наименований свою. Скопировать всё разом на всю таблицу тоже не получится, потому что нужно будет менять ссылку с названием клиента. Но это всё делается за минуту. А преимущества такого подхода очевидны:
Если в нашей сводной что-то будет смещено, то формула не сломается, в отличие от прямой ссылки.
Когда будут данные за декабрь, мы их добавляем в нашу "умную" таблицу, обновляем сводную и готово.
Если появится новый клиент или наименование, то нужно будет добавить новые строки в отчёт и скопировать формулу.
Недостатки у такого подхода, конечно, тоже имеются:
Если фильтровать сводную или полностью перелопатить её структуру, то значения в формуле будут пересчитываться в соответствии с тем, что сейчас отображается в сводной. Таким образом, например, если поставим фильтр в сводной таблице на какие-то определённые наименования, а потом забудем фильтр очистить, то в отчёте суммы по этим наименованиям потеряем (ячейки будет пустыми, ошибок не будет, ведь мы приправили всё ЕСЛИОШИБКОЙ).
Если отчёт и сводная находятся в разных книгах, то формула будет работать только тогда, когда обе книги открыты одновременно (не относится к ситуации, когда обе открыли, а потом книгу со сводной закрыли).
На этом, пожалуй, всё. Повторюсь ещё раз, я описал свой опыт. С моей субъективной колокольни, именно такое использование данной функции наиболее полезное и практичное. То, что потом на основе таких вот отчётов можно строить обычные диаграммы - это уже производное от главного, а именно от того, что основное преимущество данной функции - более менее безопасный и гибкий способ переносить данные из сводной таблицы в обычные таблицы.
Как обычно, спасибо всем, кто потратил своё драгоценное время на чтение данной статьи. Надеюсь, было полезно. И да, если уже пользуетесь этой функцией, поделитесь в комментариях, какие задачи она вам помогает решать. Наверняка есть что-то такое, чего я о ней не знаю (никогда не было стыдно признаться в том, что я чего-то не знаю). Или если при использовании данной функции вы сталкивались с какими-то критичными трудностями. Да пребудет с нами сила ИКСэль.
Алё, а отключить-то как?! На всякий случай напишу, как отключить автоматическое создание этой функции, когда ссылаемся на ячейки сводной (большинству она вот совсем не нужна). Всё просто:
Выбираем любую ячейку сводной.
Вкладка Анализ - группа Сводная таблица - раскрываем Параметры - снимаем галочку Создать GetPivotData.
Profit!!11
Больше вас это богомерзкое чудовище не побеспокоит. А если нужна будет, можете вводить её как обычную функцию.
Господа, прошу помочь. Прислали мне тут адреса не в нормальном виде Город-Адрес, а в виде сводной таблицы. И теперь надо как-то привести это всё в нормальный вид как указал выше. Как это возможно сделать?
Энтузиаст создал внутри Excel рабочий 16-битный процессор с тактовой частотой 3 Гц, 128 КБ ОЗУ, 16-цветным дисплеем с разрешением 128x128 пикселей.
Автор не использовал сторонние плагины, только стандартные функции excel. Самое впечатляющие создание собственного языка - ассемблера Excel-ASM16. Значит с excel можно работать как с настоящим процессором, а это позволяет моделировать работу пк.
Ждем уровня развития техники, когда на нем смогут запустят excel.
#без_пяти_минут_как_актуально
Ребят, подскажите, плиз.
Надо потренироваться.
Может есть такой же, как по SQL? С заданиями и примерами.
Коллеги, добрый день.
Я не уверен, что я корректно задаю вопрос. Именно от собственной неуверенности и пытаюсь что-то спросить на тему.
Вопрос такой...
Я подключил большую таблицу из базы.
Она самодостаточна, т.е. в ней есть все поля, которые мне нужны в качестве срезов. Подключил я её через power Query, импортнул в модель данных. На её основе строю сводные таблицы и графики. Пока всё норм.
Но в какой-то момент своей жизни я видел, что можно создавать собственные наборы для формирования срезов. Причём, это делалось не средствами Power Pivot, а средствами на панели Excel: Анализ сводной диаграммы -> блок "Вычисления" ->Поля, элементы и наборы данных.
Я попытался погуглить эту тему, но у меня что-то либо очень сложное, либо на каких-то узких примерах попалось.
Пожалуйста, подскажите, где набраться мудрости?
Спасибо.
В VBA Excel есть недодокументированный, но очень мощный и полезный метод Application.Match(). Есть метод-близнец WorksheetFunction.Match(). Работают они похожим образом, но возвращают разные результаты.
В VBA-коде лучше использовать именно Application.Match().
С помощью этого метода можно:
искать элементы (один или сразу несколько) в одномерных диапазонах и массивах;
сравнивать массивы и диапазоны на предмет наличия одинаковых элементов;
осуществлять точный и неточный поиск.
Скорость работы Application.Match() значительно выше, чем у циклов, перебирающих элементы массивов или, тем более, ячейки листов Excel.
Отдельно необходимо отметить, что метод этот довольно известный, но, как правило, не используемый на полную мощность. Так, многим неизвестно - и в официальной справке этого нет - что можно искать не одно значение, а массив значений. Также многие продолжают использовать неудобный вариант WorksheetFunction.Match() вместо удобного Application.Match().
С помощью Application.Match() можно, например, организовать, эффективный поиск множества подразделов на листе за одну операцию при обработке таблиц. Это позволяет сильно экономить по времени поиска и по количеству строчек кода. Также довольно просто можно применить этот метод для операций пересечения и сравнения множеств.
Application.Match(<что ищем>, <где ищем>, <как ищем>)
<Что ищем> может быть:
одно значение - переменная или константа, например «aaa», 123 или ValueToFind, причем различных типов - строка, число, дата, логическое.
текстовые значения могут содержать подстановочные знаки * (0+ любых символов),? (один любой символ). Их можно экранировать тильдой ~, если нужно найти именно * или ?
одномерный массив Array(«aaa», 123)
диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")
<Где ищем> может быть:
одномерный массив, содержащий значения различных типов, например, Array(«aaa», 123)
диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")
<Как ищем>:
0 - точно. Массив <Где ищем> может быть неупорядоченным (неотсортированным)
-1 - поиск наименьшего значения. Массив <Где ищем> должен быть отсортирован по убыванию
1 - поиск наибольшего значения. Массив <Где ищем> должен быть отсортирован по возрастанию
Application.Match() возвращает:
если <что ищем> – одно значение, то возвращается одно значение типа Double (индекс найденного значения в массиве, начиная с 1) или значение типа Error (если значение не найдено);
если <что ищем> – массив из N элементов, то возвращается массив, содержащий N значений типа Double (индекс найденного значения в массиве, начиная с 1) или значений типа Error (если значение не найдено).
Особенности:
если значение не найдено, возвращается значение ошибки #N/A [Error 2042] (метод WorksheetFunction.Match() в этом случае генерирует исключение)
поиск текста производится всегда регистронезависимо, т.е. прописные и строчные буквы А и а считаются одинаковыми, и не зависит от установки Option Compare { Binary | Text }
при успешном поиске Application.Match() возвращает для каждого искомого значения только первый индекс найденного элемента в <где ищем>. Т.е. если <что ищем> = 2,2 а в <где ищем> находится 2,1,2,2, то результатом будет массив 1,1 - обе двойки найдется в первом индексе.
Индексы нумеруются с 1 независимо от установки Option Base { 0 | 1 }.
Данный код ищет строки «qqq» (три q или Q подряд) и «a?b» (строка из трех символов, начинающаяся на a или A, заканчивающаяся на b или B с любым символом посередине) в массиве arr, содержащем строки «AAA», «A5A», «Abb».
В результате работы кода в окне Immediate будет напечатано:
Error 2042
3
Этот результат означает, что строка "qqq" не найдена в массиве arr, а "a?b" соответствует третьему элементу "Abb".
Этот код ищет значения из ячеек колонки A листа Excel в колонке C листа Excel. Результат выводится в цикле с предварительной проверкой, найден ли очередной элемент или нет. Эта проверка производится с помощью функции IsNumeric(), которая возвращает Истину в том случае, если аргумент - число. В том случае, если значение не найдено, в результирующем массиве будет находиться значение ошибки (Error), и IsNumeric() вернет Ложь. Также это можно проверить с помощью функции IsError(), которая возвращает Истину, если аргумент является значением ошибки.
Element 1 is found at index 9
Element 8888 not found (Error 2042)
Element 3 not found (Error 2042)
Element 4 not found (Error 2042)
Element 5 is found at index 4
Element 6 not found (Error 2042)
Element 7 is found at index 10
Element aaa is found at index 6
Element 9 not found (Error 2042)
Element 06.09.2022 is found at index 8
Вопрос. Есть в богомерзких гугл таблицах что-то по типу элементов управления формы, как в православном Экселе?
Нашел там только хуево сделанный выпадающий список.
Мне надо что-то вроде
Я полагаю какими-то расширениями можно сделать мб? В базе этого нет?
Мы постарались сделать каждый город, с которого начинается еженедельный заед в нашей новой игре, по-настоящему уникальным. Оценить можно на странице совместной игры Torero и Пикабу.
Реклама АО «Кордиант», ИНН 7601001509