Двач-помогач, выручай.На каждом из трёх листов: январь, февраль, март — есть по таблице (пик 1). Задача собрать формулами данные в одну таблицу (пик2). Менять структуру исходных таблиц нельзя, добавлять новые строки/столбцы тоже. В тред призываются магистры экселя и опытные перекатыватели таблиц.С меня как всегда
>>226502277расскажи лучше, как ссылки оставлять на другие листыв шараге меня обучали только оставлять ссылку на ячейки внутри одной таблицы
>>226502312Да ссылки на другие листы-то изи оставлять. Вот типа так, например. Либо я не понял о каких вы ссылкахПроблема в том, что всякие типичные функции, используемые для похожих заданий отбора, типа ВПР или Индекс-поискпоз привязаны к порядку столбцов.
>>226502404не думаю, что там полный пиздец. Раз можно обойтись простыми ссылками, то лучше так и сделать
>>226502178 (OP)ОП, анон тут кинул, как должна выглядеть примерная ссылка, так что все в твоих руках(заполнять это все ОЧЕНЬ нудно и неприятно)
>>226502498Что значит обойтись простыми ссылками? Не думаю, что задание зачтут, если я просто руками отберу в каждой таблице нужные значения и вставлю.А если там 984923849 строк будет? И в задании написано "формулами".
>>226502624Мне там для одного из заданий вот такую хуйню писать пришлось, я чуть не ебанулся.Хз, возможно, это всё костыли и я просто не продвинутый пользователь экселя, конечно.
>>226502178 (OP)Обдумай сначала логически, как можно на формальном языке описать преобразования из одних таблиц в другую. В экселе легко работать с функциями, т.к. они все сгруппирированы. В крайнем случае дрочи VBA и макросы, с этим что угодно сможешь захуярить.
>>226502178 (OP)тэкс, ничего умнее такой херни придумать не мог, сорянразве что подрубать макросы или надстройки. в остальных случаях дрочить руками
>>226503099Да я уже думал, анон, но логически-то я примерно представляю как описать, а вот на языке формул хз.>>226503148Анлаки. Но спасибо за попытку!
>>226503258>а вот на языке формул хзЯзыком формул вообще всё что угодно можно описать, а уж мощностей экселевского набора функций точно должно для этого хватать. Либо ты тупой и не можешь адекватно сформулировать алгоритм преобразования одной структуры данных в другую, либо ты тупой и не способен найти нужную экселевскую формулу. Третьего не дано.
Смотри какая хуйня - тут есть объединенные ячейки в шапке сводной таблицы. Вы учились объебывать систему в таких случаях? Потому что там два стула - на одном оче хуевая формула, которую тебе будет сложно понять задающее смещение относительно текущей ячейки в зависимости от того является ли она первым, вторым или третим столбиком под объединенной ячейкой, на втором стуле - манипуляции с таблицей которые в зависимости от строгости препода он сочтет или НЕ сочтет изменением структуры таблицы - нужно перезаписать шапку таблицы особым образоммимо-магистр-эскеля
>>226508026ХмммХз, а разве если задать не B2:D2, а просто B2 работать не будет? Все предыдущие формулы, которые я пробовал, работали.>>226508529Нихуя себе. Вот этого я точно не ожидал. Во-первых, что помогут. Во-вторых, что формула будет такого размера.Спасибо, анон.
>Хз, а разве если задать не B2:D2, а просто B2 работать не будет? Все предыдущие формулы, которые я пробовал, работали.Если задать B2 то ты потом формулу откопировать не сможешь, потому что будешь жестко привязан к одной ячейке как >>226508903 анон. Именно из-за этого он пишет 4 формулы.В идеале всё задание выполняется одной формулой которую ты просто копируешь во все ячейки таблицы. >>226508903 анон близок, и формально выполнил задачу, но сделал это через четыре формулы и условные операторы вместо горизонтального и вертикального просмотра и ссылки на имя листа из шапки- не изящно.Респект ему, тем не менее. Возможно тебя устроит его решение. Самому участвовать в этом цирке мне бы не хотелось.мимо-магистр-экселя
>>226509548Я сам высрал что-то похожее на присланное аноном.Правда, руками нужно менять вид услуги и месяца, но это хоть что-то. Теперь вот думаю каким решением лучше воспользоваться.
На самом деле, пиздец как не люблю эксель как раз из-за громоздких формул.Все вот эти обращения к другим листам с апострофами, дополнительные условия в кавычках, куча скобок дико усложняют восприятие формулы и понимание, что же там в итоге написано. И всё это ещё в одну строку засунуто.
>>226509819Ты на верном пути. Теперь надо избавиться от прямого указания имени листа и брать его из шапки таблицы ну и таки решить что делать с объединенной ячейкой вверху, к которой ты тоже жестко привязываешься (B2)Формула через которую объебывается объединенная ячейка без вторжения в структуру таблицы (даже невидимого глазу) выглядит вот так=INDEX($1:$2,1,COLUMN()-MOD(COLUMN()-1,3))вычитает из номера текущего столбика остаток от деления номера столбика на 3 (так как у нас три столбика объединено) таким образом ссылаясь из любого столбика (X2,X3,X4) на ячейку B2
Сюк, как неаккуратно написал, аж сам подгорелв формуле неправильно строчку выделил - правильно =INDEX($2:$2..... - вот так:=INDEX($2:$2,1,COLUMN()-MOD(COLUMN()-1,3))Ну и цифра после MOD(COLUMN()- будет в твоем случае скорее всего 2 (но варианта три: 0, 1 или 2)
>>226510392>>226510836Жесть ты крут, получается.Спасибо, запомню на будущее.Посоветуй тогда что ли, как Экселем преисполниться, как ты. Какие гайды курить, что читать, мб курс какой есть. А то те ресурсы, которые я находил, либо слишком для пожилых тётенек, либо наоборот макросы-VBA во все поля.
>>226511973Им не надо преисполняться, он оче, максимально хуевый. Лучше курить в сторону алгоритмов программирования и баз данных в том плане что там ты научишься формализовать задачу и оценивать набор данных с которым тебе предстоит возиться - видеть такие подъебки как тут - перепутаные очередности столбцов и строчек (и вообще не привязываться к очередности потока данных). С этими знаниями тебе останется только погуглить какими формулами реализуется формализованная тобой задача конкретно в экселе.Если ты таки сделал всё одной формулой, то знай - я очень горд тобой, выкладывай ответ в тред и иди бери медаль с верхней полки.
Не жалеешь ты старика. Неужто самому придется браться за дело? Где горизонтальный просмотр и ссылка на название листа из шапки таблицы?
>>226512487Я думал бросить это дело, честно говоря, но раз уж ты помогаешь, решил допинать таки.С помощью твоей формулы и гугла, кажется, избавился от привязки к названиям столбцов. Сейчас пойду гуглить как названия листов забирать из шапки.
>>226513356Да я просто не спал ночь, там ещё 5 похожих заданий было, в которых я дико костылял, как мог. Так что сейчас тяжело соображается.
Написал уже. Раззадорили вы меня.Под спойлерам подсказка о том какие функции я использовалГПРИНДЕКССТОЛБЕЦОСТАТДВССЫЛСЦЕПИТЬПОИСКПОЗ
>>226513783Погуглил что некоторые из этих формул делают.Я так понимаю, логика в том, чтобы задать имя нужного листа через ДВССЫЛ, засунув туда январь-февраль. Не понял пока зачем нужен ГПР и СЦЕПИТЬ.
Подсказка №2Неудобно использовать совместно ГПР и ВПР, поскольку одним из аргументом обоих функций является номер строки/столбца. Номер строки/столбца проще получить через ПОИСКПОЗ. т.е. я либо иду через ГПР и нахожу через ПОИСКПОЗ номер строки либо чекрез ВПР и нахожу через ПОИСКПОЗ номер столбцачерез СЦЕПИТЬ сцепляю название листа из шапки к которому хочу обратиться, восклицательный знак и диапазон к которому хочу обратиться на том листе - передаю это всё в ДВССЫЛ
Подсказка №3В функциях ГПР, ВПР и ПОИСКПОЗ важны необязательные параметры "интервальный просмотр" и "тип сопоставления"
>>226514719Так, с ДВССЫЛ я, вроде бы, разобрался, это не так сложно.Массив с листа "январь" я теперь получаю.Теперь мне нужно в этом массиве искать нужные мне значения, если я правильно понимаю. Как раз через гпр-поискпоз и получением значения из объединённой ячейки.Я вообще не соображаю, на самом деле. Чисто на морально-волевых. Не уверен, что смогу собрать теперь это все говно в одну формулу. Я попытаюсь, но не хочу тебя мучить если что.
>>226515211За меня не беспокойся, я матёрый и терпиливый объяснятель. Доводилось объяснять эксель и бабушками которые от слова "формула" в обморок падают.Если доделаешь я бы порадовался (и таким образом компенсировал вложеные усилия), а если не сдюжишь - я пойму.
А почему у тебя остаток от деления на один-то вдруг у нас же 3 ячейки сгруппировано? и ты уверен что от остатка надо 1 отнять (а не два или ноль)?откопируй строчку этой формулы под таблицей должно бытьтелефоны-телефоны-телефоны-планшеты-планшеты-планшеты-услуги-услуги-услуги-sim карты-sim карты-sim карты
>>226516480Потому что я посмотрел твою формулу+формулу на стаковерфлоу, потыкал числа, для января получилось и я их не задумываясь оставил. А разбираться что и куда начал только сейчас.Но!Оно работает, анон! Для всех ячеек. Сначала протянул по горизонтали, а потом по вертикали для всех столбцов.Спасибо тебе большое!
>>226516795Вот теперь я верю, что всё работает. От греха подальше во втором ПОИСКПОЗ добавь тоже параметр "интервальный просмотр"Мой вариант немного отличается, но я бы на месте препода не доебался бы уже. Как я говорил, я сразу пихаю все в ГПР или ВПРМой вариант:=ГПР(ИНДЕКС($2:$2;1;СТОЛБЕЦ()-ОСТАТ(СТОЛБЕЦ()-2;3));ДВССЫЛ(СЦЕПИТЬ(B$3;"!A1:E7"));ПОИСКПОЗ($A4;ДВССЫЛ(СЦЕПИТЬ(B$3;"!A1:A7"));0);ЛОЖЬ)Рад был помочь разобраться
>>226517294>добавь тоже параметр "интервальный просмотр"Но там же стоит 0, вроде бы, или я уже что-то не понимаю?>на месте преподаНа самом деле, это одно из 6 тестовых заданий на стажировку>сразу пихаю все в ГПР или ВПРВыглядит более красиво, да.
>>226517696>или я уже что-то не понимаю?Если есть значит это я в количестве аргументов проебался (на глаз же компилировал)>На самом деле, это одно из 6 тестовых заданий на стажировкуУдачной тебе стажировки, тогдаДвач обучающий (в кои-то веки)С наилучшими пожеланиями,Мимо-магистр-экселя
>>226518031Да ничего, я сам уже ни в чём точно не уверен.Спасибо, надеюсь, возьмут, не смотря на все мои костыли в остальных заданиях. Первые два там были прям совсем-совсем простые. Но вот над остальными всю ночь пердолиться пришлось.Но я, честно говоря, даже не подозревал о существовании в экселе формул, которые могут возвращать имя листа, или возвращать данные по его называнию и т.д. Теперь буду смотреть немного по-другому на него и задачи тоже.Тебе тоже всего хорошего!
