Сообщество - MS, Libreoffice & Google docs

MS, Libreoffice & Google docs

754 поста 14 958 подписчиков

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

7

Построение графика функци

Добрый день!

Подскажите, пожалуйста, есть ли способ зафиксировать соотношение осей точечной диаграммы 1:1, чтобы при изменении максимального значения оси расстояние между вертикальными и горизонтальными линиями сетки оставалось одинаковым?

Требуется, чтобы на печати угол наклона графика соответствовал расчетному. Сейчас опытным путём подгоняю для каждого диапазона размер диаграммы/области построения. У меня есть несколько диаграмм, для одних требуется менять менять диапазоны построения по обеим осям, в других только по Х, а Y не меняется.

4

Але, Бен? Это Данила. Ай нид хэлп

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

7

Power Pivot не отображает данные за предыдущий период в сводной таблице

Ребя, помогите, плиз, немного растерялся.
Прошу прощения за длину файла, не получается сжать
Ссылка на файл на драйве.

Ситуация такая, что есть таблица с данными за определённые годы.
И есть таблица справочник по годам. Они связаны.
Я пытаюсь в текущем году показать данные за предыдущий год, написав такую меру:

Рентабельность собственного капитала (по чистой прибыли):= VAR CurrentYear = MAX('Годы'[Год]) VAR PrevYear = CALCULATE(MAX('Годы'[Год]); 'Годы'[Год] = CurrentYear - 1) VAR CurrentSK = CALCULATE(  SUM('Перечень_балансов'[Значение]);  FILTER('Перечень_балансов'; [Код строки] = 1300 && 'Годы'[Год] = CurrentYear) ) VAR LastSK = CALCULATE(  SUM('Перечень_балансов'[Значение]);  FILTER('Перечень_балансов'; [Код строки] = 1300 && 'Годы'[Год] = PrevYear) ) RETURN LastSK

И вроде бы всё правильно. НО! В сводной таблице, построенной в логике: Предприятия по строкам, Годы (из таблицы справочника) по столбцам, не отображается результат.

При этом, если я вывожу в таблицу PrevYear  - всё нормально. Годы показываются.


Я не могу понять, что я делаю не так, что он отказывается показывать значения.

Подскажите, плиз.

Спасибо.

17

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

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

67

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

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

Ссылка на файл (с уже прописанными формулами и одним листом, где можете попробовать прописать самостоятельно) - 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
13

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

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

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

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

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

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

Спасибо.

5

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
Отличная работа, все прочитано!