Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Евгений Иванов
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 След.
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 19:02:47
Jack Famous, здравствуйте, благодарю за ссылку!
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 19:00:48
Тимофеев, работает! Интересное решение с ФИЛЬТР(A1:A11;A1:A11<>0)... не догадывался. Буду разбираться!
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 17:24:19
Павел с Востока, есть из чего выбрать теперь  :D . Спасибо, одной формулой элегантней, зато тут сразу работает даже если наименований меньше. Формулу то легко поправить тоже, в голове крутится, но не поймаю)
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 17:20:08
Тимофеев, один ньюанс. Если в диапазоне не 10 наименований, а меньше (больше 10 не бывает). Можно как то исключить пустые ячейки или диапазон по которому работает формула сделать динамическим в зависимости от количества наименований? Напрашивается что то типа СЧЁТЗ в комплекте со СМЕЩ. Может через именованый диапазон? Но не выходит  :( . Пробовал умную таблицу, но не прокатило.
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 16:14:51
Тимофеев, ох красота. Всё работает, плюс красиво!  :)
Гигантское спасибо! Сам я уже который день бился.
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 15:42:48
Алексей Абраменко, интересно! Всё при этом правильно работает. Попробую сократить количество дополнительных столбцов. Большое спасибо!  :)
Одной формулой тут конечно вряд ли обойтись.
Изменено: Евгений Иванов - 21.03.2023 15:46:36
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 15:35:46
Alien Sphinx, согласен с вами, добавлю в текст сообщения версию Excel. Но всё равно спасибо, пойду пробовать с Power Query!
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 15:18:58
Цитата
написал:
2021 лямбду не тянет
Да, перепроверил, к сожалению пока поддерживается только 365.
Я так понимаю если есть формула с лямбдой, то можно и без оной? Только более громоздко и нудно?
Изменено: Евгений Иванов - 21.03.2023 15:23:56
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 15:13:52
Alien Sphinx, извиняюсь не сообразил про Power Query, пару раз с ним работал, подумал макрос.
А по варианту #3 у меня такая нерабочая формула в ячейке. Excel 2021 с последним обновлением, даже сейчас перепроверил.

=LET(items_col; Table1[Наименование]; items; УНИК(items_col); numbers; Table1[ст. №]; res_column; _xlfn.MAP(items; _xlfn.LAMBDA(_xlpm.a; _xlpm.a & " ст. № " & ОБЪЕДИНИТЬ(", "; ; ФИЛЬТР(numbers; items_col = _xlpm.a)))); ОБЪЕДИНИТЬ(", "; ; res_column))
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 14:44:36
Спасибо за макрос! А реально ли это сделать формулой?
 
Перейти    
Выбрать уникальные значения и склеить их с порядковыми номерами
 
#
21.03.2023 14:19:26
Здравствуйте, прошу помощи в следующем вопросе.
В одном столбце есть список оборудования, необходимо выбрать из него уникальные наименования через запятую, но каждое наименование надо склеить с соответствующим ему номером из второго. Проще говоря убрать лишние повторы в наименованиях. Образец прикладываю. Очень желательно формулой. Excel 2021.
Необходимый результат: КСВМ ст. № 1, 4, 5, КСПМ ст. № 2, 3, 6, ДКВР ст. №7, ТВГ-1,5 ст. № 9, 11, 14
Изменено: Евгений Иванов - 21.03.2023 15:34:10
 
Перейти    
Проблема при вычислении коэффициентов линии тренда, Формула массива выдаёт ошибку при наличии в данных пустых ячеек
 
#
16.08.2022 17:41:05
МатросНаЗебре, отлично, спасибо, проблема решена! Сам что-то не допёр.
 
Перейти    
Проблема при вычислении коэффициентов линии тренда, Формула массива выдаёт ошибку при наличии в данных пустых ячеек
 
#
16.08.2022 15:01:14
Уважаемые знатоки!
При вычислении коэффициентов линий тренда столкнулся с проблемой: при используемых формулах массива при наличии в диапазоне данных пустых ячеек формула выдаёт ошибку. Для упрощения работы диапазоны названы Х и Y и изменить их размер не желательно, т. к. данных всё время разное количество, а подгонять каждый раз формулы коэффициентов под разное количество данных трудозатратно. Линейное уравнение при этом работает отлично, полиноминальное второй степени и др. выдают ошибку.
Возможно ли как то вычислить коэффициенты полиноминальной линии тренда (жёлтые ячейки) при наличии пустых ячеек в данных (X и Y)? Красным выделен необходимый результат без именованных диапазонов и пустых ячеек.
 
Перейти    
Ссылки на ещё не созданные листы
 
#
07.07.2022 13:38:03
Ігор Гончаренко, все мы ошибаемся и можно прямо указать на ошибку, не все понимают витиеватые речи)
Извините за непонятливость)
 
Перейти    
Ссылки на ещё не созданные листы
 
#
07.07.2022 13:16:51
Xel, понимаю, просто эта таблица строго установленной формы государством. А в вопросе ДВССЫЛ потому и просил не использовать, но с не созданными листами работать может только он видимо. С ним решение и нашёл кстати _Boroda_.
Если можно обойтись без него буду очень признателен)

Цитата
написал:
Задача то именно свод сделать.
там и надо то выдернуть с каждого листа по одному числу.
 
Перейти    
Ссылки на ещё не созданные листы
 
#
07.07.2022 13:11:25
_Boroda_, выражаю благодарность! Всё работает как надо! Получается она просто ищет необходимую строку по словам.
Я думал есть какой то вариант через СМЕЩ ещё, считать сколько прибавилось или убавилось строк и смещать результат. Но тут элегантней!
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 18:14:41
Xel, вот что я имел в виду:
Цитата
написал:
При этом количество строк на вновь созданном листе может измениться и ячейка I38 станет I40.Если обычно эксель "понимает" когда мы прибавляем строки и сам переписывает формулу, то через ДВССЫЛ нет и она покажет на ту же ячейку, с неправильными данными.
Просто довольно сложно объяснить на примере с несуществующими листами)
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 17:51:20
_Boroda_, Vik_tor, теперь я понял в чём мой косяк, Игорь как то весьма расплывчато, но красиво говорит.

Выкладываю конкретный пример. В лист ЭЭ ячейки D3:D14 собираются данные с листов С1, С2, С3 и т.д. ячеек I38.
Т.е. в D3 данные из С1, в D4 данные из С2 и т.д.
Лист ЭЭ и С1 сразу существуют в книге, далее С1 копируется, немного корректируется и переименовывается в С2. И так до необходимого количества листов. При этом количество строк на вновь созданном листе может измениться и ячейка I38 станет I40.
Если обычно эксель "понимает" когда мы прибавляем строки и сам переписывает формулу, то через ДВССЫЛ нет и она покажет на ту же ячейку, с неправильными данными.
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 16:24:49
Ігор Гончаренко, изначально в вопросе я всё прописал. Что конкретно не получается с ДВССЫЛ и даже просил без оной. Надо просто читать и вопрос, а не только название темы. В названии темы все нюансы разве уместишь, зачем тогда сам вопрос? За удачу спасибо!
Цитата
написал:
Помогает ДВССЫЛ, но формула получается статической, при добавлении строк путь оказывается неверен.Пример прикладываю.Существует ли какое решение без ДВССЫЛ и по возможности макросов?
Цитата
написал:
Кол-во строк тоже немного может меняться (почему ДВССЫЛ плохо подходит).
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 15:43:05
Ігор Гончаренко, такое же решение уже предлагал Xel, что неплохо работает для таблиц где неизменно количество строк. Однако весь смысл excel теряется, если не можешь добавить ещё строку или столбец и вся формула не работает. Получается калькулятор с ручным пересчётом просто.
Цитата
Xel, написал:
=ЕСЛИОШИБКА(ДВССЫЛ(A3&"!A1");0)
Цитата
Евгений Иванов, написал:
принципиальной разницы с формулой =ДВССЫЛ("Лист2!A1") нет. И при смещении строк или столбцов ДВССЫЛ не увидит данных в ячейке, как будто она закреплена $$. При добавлении строки допустим данные перейдут в ячейку А2, но ДВССЫЛ их не увит, у него А1 прописана. Вы просто убираете ошибку когда листа нет, но это не существенно.ДВССЫЛ тут не подходит видимо.
У меня сейчас так и работает через ДВССЫЛ, но ограничен тем что нельзя менять размеры таблиц(
Но спасибо!
Изменено: Евгений Иванов - 06.07.2022 15:46:47
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 13:54:45
Ігор Гончаренко, планировался образец для заполнения на 5 листов и получившиеся данные с 5 листов выборочно собираются на один лист. Но изначально в книге только Лист1, который заполняется, потом копируется как Лист 2 изменяется и т.д. до 5 каждый лист последовательно немного изменяется от предыдущего. Кол-во строк тоже немного может меняться (почему ДВССЫЛ плохо подходит).
И вот на том листе куда всё собирается в итоге нельзя заранее прописать ссылки на Лист2, 3... т.к. их пока ещё нет.
А если таких файлов с десяток в день проходит и это сильно бы упростило работу.
Изменено: Евгений Иванов - 06.07.2022 14:02:06
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 13:44:48
Ігор Гончаренко, попробую упростить задачу, может так будет ясней: в книге есть два Листа 1 и 2. В Листе 1 ссылка на любую ячейку Листа 2 типа простой =Лист2!A1.
При удалении Листа2 из книги ссылка ломается и приобретает вид =#ССЫЛКА!A1, вместо того что бы выдать просто ошибку. Соответственно опять добавив Лист 2 мы не получим на Листе 1 никаких значений, ссылка исчезает.
Похожее происходит и если прописать на Листе 1 =Лист2!A1 заранее, т.е. пока Лист 2 не создан. Создать его, но ссылка при этом работать не будет. Не странно ли?
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 13:26:48
Ігор Гончаренко, да, не так я выразился. Подходит, но решает только половину проблемы. Может в новых версия офиса добавят такую возможность.
Для меня странно что при удалении листа ссылки на него "ломаются" навсегда. Даже если потом этот лист вернуть. Как бы вот он, ссылки то рабочие, давай работай)
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 13:20:55
evgeniygeo, этот вариант ещё лучше, благодарю!
Видимо придётся всё же с макросами)
Интересно что при удалении, допустим, Листа4 и опять его добавлении ссылка ломается.
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 13:15:47
Xel, принципиальной разницы с формулой =ДВССЫЛ("Лист2!A1") нет. И при смещении строк или столбцов ДВССЫЛ не увидит данных в ячейке, как будто она закреплена $$. При добавлении строки допустим данные перейдут в ячейку А2, но ДВССЫЛ их не увит, у него А1 прописана. Вы просто убираете ошибку когда листа нет, но это не существенно.
ДВССЫЛ тут не подходит видимо.
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 11:00:09
evgeniygeo,
спасибо! Как вариант можно.
Странно что стандартными средствами это сделать наверно невозможно.
Ну выдавала бы формула ошибку без листа, а с появлением его начинала работать.
Изменено: Евгений Иванов - 06.07.2022 11:09:29
 
Перейти    
PQ. Число прописью
 
#
06.07.2022 10:53:15
Извиняюсь не увидел что надо в PQ.
Пробуйте эту формулу (где i21 ячейка с числом):
Код
=@ИНДЕКС({"";"сто ";"двести ";"триста ";"четыреста ";"пятьсот ";"шестьсот ";"семьсот ";"восемьсот ";"девятьсот "};ОСТАТ(ОТБР(I21/10^8);10)+1)&@ВЫБОР(ОСТАТ(ОТБР(I21/10^7);10)+1;"";ИНДЕКС({"десять ";"одиннадцать ";"двенадцать ";"тринадцать ";"четырнадцать ";"пятнадцать ";"шестнадцать ";"семнадцать ";"восемнадцать ";"девятнадцать "};ОСТАТ(ОТБР(I21/10^6);10)+1);"двадцать ";"тридцать ";"сорок ";"пятьдесят ";"шестьдесят ";"семьдесят ";"восемьдесят ";"девяносто ")&@ЕСЛИ(ОСТАТ(ОТБР(I21/10^7);10)<>1;ИНДЕКС({"";"один ";"два ";"три ";"четыре ";"пять ";"шесть ";"семь ";"восемь ";"девять "};ОСТАТ(ОТБР(I21/10^6);10)+1);"")&ЕСЛИ(ОСТАТ(ОТБР(I21/10^6);1000);"миллион"&ЕСЛИ(ОСТАТ(ОТБР(I21/10^7);10)=1;"ов ";ВПР(ОСТАТ(ОТБР(I21/10^6);10);{0;"ов ":1;" ":2;"а ":5;"ов "};2));"")&@ИНДЕКС({"";"сто ";"двести ";"триста ";"четыреста ";"пятьсот ";"шестьсот ";"семьсот ";"восемьсот ";"девятьсот "};ОСТАТ(ОТБР(I21/10^5);10)+1)&@ВЫБОР(ОСТАТ(ОТБР(I21/10^4);10)+1;"";ИНДЕКС({"десять ";"одиннадцать ";"двенадцать ";"тринадцать ";"четырнадцать ";"пятнадцать ";"шестнадцать ";"семнадцать ";"восемнадцать ";"девятнадцать "};ОСТАТ(ОТБР(I21/1000);10)+1);"двадцать ";"тридцать ";"сорок ";"пятьдесят ";"шестьдесят ";"семьдесят ";"восемьдесят ";"девяносто ")&@ЕСЛИ(ОСТАТ(ОТБР(I21/10^4);10)<>1;ИНДЕКС({"";"одна ";"две ";"три ";"четыре ";"пять ";"шесть ";"семь ";"восемь ";"девять "};ОСТАТ(ОТБР(I21/1000);10)+1);"")&ЕСЛИ(ОСТАТ(ОТБР(I21/1000);1000);"тысяч"&ЕСЛИ(ОСТАТ(ОТБР(I21/10^4);10)=1;" ";ВПР(ОСТАТ(ОТБР(I21/1000);10);{0;" ":1;"а ":2;"и ":5;" "};2));"")&@ИНДЕКС({"";"сто ";"двести ";"триста ";"четыреста ";"пятьсот ";"шестьсот ";"семьсот ";"восемьсот ";"девятьсот "};ОСТАТ(ОТБР(I21/100);10)+1)&@ВЫБОР(ОСТАТ(ОТБР(I21/10);10)+1;"";ИНДЕКС({"десять ";"одиннадцать ";"двенадцать ";"тринадцать ";"четырнадцать ";"пятнадцать ";"шестнадцать ";"семнадцать ";"восемнадцать ";"девятнадцать "};ОСТАТ(ОТБР(I21);10)+1);"двадцать ";"тридцать ";"сорок ";"пятьдесят ";"шестьдесят ";"семьдесят ";"восемьдесят ";"девяносто ")&@ЕСЛИ(ОТБР(I21)=0;"ноль ";ЕСЛИ(ОСТАТ(ОТБР(I21/10);10)<>1;ИНДЕКС({"";"один ";"два ";"три ";"четыре ";"пять ";"шесть ";"семь ";"восемь ";"девять "};ОСТАТ(ОТБР(I21);10)+1);""))&"рубл"&ЕСЛИ(ОСТАТ(ОТБР(I21/10);10)=1;"ей";ВПР(ОСТАТ(ОТБР(I21);10);{0;"ей":1;"ь":2;"я":5;"ей"};2))&ТЕКСТ(ОТБР((I21-ОТБР(I21)+0,00001)*100);" 00\ коп.;;")
Изменено: Евгений Иванов - 06.07.2022 10:56:10
 
Перейти    
Ссылки на ещё не созданные листы
 
#
06.07.2022 10:47:32
Здравствуйте, прошу помощи следующем вопросе. Возможно ли как создавать ссылки на ещё не созданный лист excel.
На примере - есть общая таблица и она должна собирать данные с Лист1, Лист2, и т.д. но в начале работы существует только Лист1. Лист2 и прочие создаются копированием и изменением Листа1. Названия листов заранее известны, но формулу написать невозможно на пока ещё не созданный Лист.
Помогает ДВССЫЛ, но формула получается статической, при добавлении строк путь оказывается неверен.
Пример прикладываю.
Существует ли какое решение без ДВССЫЛ и по возможности макросов?
 
Перейти    
Скрытие листа при деактивации
 
#
08.12.2020 16:36:47
Да, тоже работает. Благодарю!
 
Перейти    
Скрытие листа при деактивации
 
#
08.12.2020 16:31:49
Всё верно, спасибо!
 
Перейти    
Страницы: 1 2 След.
Наверх