Maz Дед Мазай Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Просьба к посетителям топика дополнять шапку наиболее часто задаваемыми вопросами со ссылками на ответы в обязательном порядке   Смена представления имен столбцов с буквенного на цифровое и обратно Сумма прописью Надстройка "PROP" (сумма прописью) надстройка Excellerator (c) Michael Zemlanukha & Maxim Shemanarev макрос Num2String Список соответствия имен функций в английской и русской версиях Excel или здесь Описание Microsoft Excel File Format (eng.) Горячие клавиши в Excel (табличка) краткая, но емкая инструкция для чайников по добавлению готовых макросов (на примере макросов по увеличению размера шрифта на X пунктов от мемебера ferias   Предыдущие ветки топика: Часть 1 | Часть 2 | Часть 3 | Часть 4| Часть 5   Все вопросы по программированию (макросы, скрипты, пользовательские функции и т.п.) обсуждаются в теме Excel VBA в разделе Прикладное программирование.   При необходимости выложить скриншот, пользуйтесь сервисом ImageShack® (ограничение на размер файла <1.5 Mb)   Книга Джон Уокенбах "Подробное руководство по созданию формул в Microsoft Office Excel 2002" PDF 34МБ Файлы к книге Смежные темы: Программы » Microsoft Office 2019 & 365 | 2016 | 2013 | 2010 | 2007 | 2003 Программы » OneNote | Outlook 2013 & 2016 & 2019 | Outlook 2010 | Microsoft Mathematics & Math Solver Программы » Word FAQ | Access FAQ Прикладное программирование » Word VBA | Excel VBA | Access VBA   Андеграунд » Microsoft Office 2019 | 2016 | 2013 | 2010 | 2007 | 2003 Андеграунд » OneNote | Visio | SharePoint Server | Project Server | Exchange Server Андеграунд » Надстройки (add-ins) и коммерческие макросы Excel Андеграунд » Самостоятельная сборка дистрибутивов Оffice 2007/2010/2013/2016 | MUI для Office 2007 Всего записей: 39223 | Зарегистр. 26-02-2002 | Отправлено: 08:02 27-03-2018 | Исправлено: Komandor, 00:39 01-01-2023

SFC Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Такая проблемка.   Оф 2019. В файле есть формула MAXIFS (МАКСЕСЛИ) Если открывать файл запуском файла, то Excel не понимает этой формулы. Она появилась начиная с 2016 года. Если сначала открывать сам Excel, а потом уже открывать этот файл, то открывается и считается норм.   Что уже только не делал. И переносил листы в новую книгу созданную новым экслем, и сохранял файл как бинарный и тд. Все равно при открытии файла формулы не понимает. Только надо сначала открывать сам Excel.   Даже вновь созданный файл с этой формулой и сохраненный не открывается корректно. Мысли есть ? Всего записей: 1669 | Зарегистр. 21-01-2003 | Отправлено: 14:00 15-06-2021

LaCastet Silver Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору SFC Цитата: Мысли есть ? Возможно, запуском другой Excel, не из MS Office 2019 открывается. ---------- Пиво — это еще одно доказательство того, что Господь любит нас и хочет, чтоб мы были счастливы! Всего записей: 4669 | Зарегистр. 27-07-2005 | Отправлено: 16:32 15-06-2021

masgak Silver Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору del Всего записей: 2910 | Зарегистр. 11-05-2005 | Отправлено: 17:55 26-06-2021 | Исправлено: masgak, 08:21 27-06-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: немного тут: про сортировку формулой (и там в конце ссылочки есть на варианты - массивы и проч.) тут ещё столкнулся с еще тупью, которую не получается победить...   Если сортируются числа формата 01-12 01-13 02-05 05-04 01-08 вперемешку с текстом или пустыми (прочерками) - формулу сортировки текста   countif(C:C,"<"&C3)+countif(C$3:C3,C3) первые нули подвешивают.   В смысле, всем рисует 1 место.   пробовал менять нули на апостроф - та же пурга. Без ничего - ёксель радостно конвертит числа в даты..     как его победить? Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 16:09 19-07-2021

ALeXkRU Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору bredonosec Цитата: вперемешку с текстом или пустыми (прочерками) - формулу сортировки текста   countif(C:C,"<"&C3)+countif(C$3:C3,C3)   первые нули подвешивают.     пример файла бы.. чтобы видно, что такое Цитата: вперемешку с текстом или пустыми (прочерками) и что не так, и как надо.. Чтобы не гадать..   Цитата: ёксель радостно конвертит числа в даты..   формат ячеек - текстовый же? Всего записей: 11933 | Зарегистр. 03-12-2003 | Отправлено: 17:35 19-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: формат ячеек - текстовый же? разумеется. Возможно, при групповой замене он его меняет, но визуально это не видно.     Цитата:  пример файла бы.. чтобы видно, что такое   https://www.file-upload.com/3qgrfxcpa0aj вот кусок вырезал.   Цитата: и что не так, и как надо.. дык - Цитата: В смысле, всем рисует 1 место.   Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 09:26 20-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору bredonosec Попробуйте задать явный текстовый формат ячеек поставив перед нулями апостроф.   Предполагая что данные в столбце А: '01-12 '01-13 '02-05   и т.д.   Для сортировки примените следующую формулу: =IFERROR(INDEX(A$1:A$99;AGGREGATE(15;6;ROW($A$1:$A$100)/(A$1:A$100=A1);COUNTIF(A$1:A1;$A$1:$A$99)));"")&"" У меня в качестве разделителя точка с запятой, замените на запятые. Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 14:16 20-07-2021 | Исправлено: Strogg, 14:17 20-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: AGGREGATE(15;6;ROW($A$1:$A$100)/(A$1:A$100=A1);   Я что-то логику этой формулы не улавливаю.. Берется функция смолл, игнорить ошибки, массив столбец А.. а дальше не понял. Как мы массив делим? Или что это за операция? И перечисление функций через запятую не понял.. Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 15:13 27-07-2021

ALeXkRU Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору bredonosec Цитата: перечисление функций через запятую не понял..     имхо, это просто: разделитель аргументов в функциях в рус.локали - ; а в eng - запятая (т.е. чтобы перевести Цитата: countif(C:C,"<"&C3)+countif(C$3:C3,C3)   на рус.эксель, нужно не только функции заменить на русские, но и запятые на точку с запятой) Всего записей: 11933 | Зарегистр. 03-12-2003 | Отправлено: 17:11 27-07-2021 | Исправлено: ALeXkRU, 17:12 27-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата:  имхо, это просто: разделитель аргументов я не об этом. Я о том, как перевести смысл выражения Strogg.     Цитата: на рус.эксель, нужно у меня английский, это неактуально   Актуально понять формулу )) Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 18:37 27-07-2021

ALeXkRU Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: Я о том, как перевести смысл выражения   хмм.. у него ж там нет про запятые...   Цитата: перечисление функций через запятую не понял..   Всего записей: 11933 | Зарегистр. 03-12-2003 | Отправлено: 19:09 27-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору bredonosec Цитата: Берется функция смолл, игнорить ошибки, массив столбец А.. а дальше не понял. Как мы массив делим? Берутся номера строк ROW(A1:A, скажем, 100) берется столбец с данными и критерий, функция находит совпадения (TRUE) затем делением (мат. операция TRUE/number) получает номер строки. Ошибки при этом игнорируются, как идеологически вредные.   Далее, как и в функции НАИМЕНЬШИЙ выводится коэффициент положения в диапазоне, 1, 2, 3 и т.д. Не скажу, что справка дает исчерпывающие ответы, но принцип работы проясняет. Идея же использования этой части COUNTIF(A$1:A1;$A$1:$A$99) заключается в создании массива констант по возрастающей (или убывающей в зависимости какой параметр задан для AGGREGATE), для последующей сортировки.   НО... Поскольку мы имеем дело с псевдочислами, то скорее всего, эта конструкция будет работать некорректно. Могу предложить совершенно тантрическую массивную формулу, основанную на матричном представлении данных от Майка Гирвина, много лет назад подсмотренную у него на канале, которая работает со смешанными данными (число/текст): Цитата: =INDEX($A$2:$A$20,INDEX(MODE.MULT(IFNA(MATCH(ROW($A$2:$A$20)-ROW($A$1),MMULT(($A$2:$A$20<>"")*($A$2:$A$20>=TRANSPOSE($A$2:$A$20)),1^ROW($A$2:$A$20)),{0,0}),"")),ROW(A1))) Пример выложил на обменник. Линк будет действителен в течение 7 дней.   Update. На одном из форумов нашел еще формулу: Цитата: =IFERROR(INDEX(A$2:A$20,MATCH(ROW(A1)+COUNTIF(A$2:A$20,""),MMULT(--(IF(A$2:A$20>=TRANSPOSE(A$2:A$20),ROW(A$2:A$20),)=ROW(A$2:A$20)),--ISNUMBER(ROW(A$2:A$20))),)),"") С обоими формулами есть ньюанс. Дубли, то бишь одинаковые значения будут игнорироваться. Если в списке есть два значения типа 01-08, то будет выведено лишь одно.       Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 21:27 27-07-2021 | Исправлено: Strogg, 03:22 28-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: Не скажу, что справка дает исчерпывающие ответы, но принцип работы проясняет.   я собсно по ней и пытался понять, когда решился спросить )))     Цитата: Для сортировки примените следующую формулу: =IFERROR(INDEX(A$1:A$99;AGGREGATE(15;6;ROW($A$1:$A$100)/(A$1:A$100=A1);COUNTIF(A$1:A1;$A$1:$A$99)));"")&""   попробовал, не понимает, как текст отображает. сменил на запятые - ничего не изменилось..     Цитата: Дубли, то бишь одинаковые значения будут игнорироваться.   хм.. а вот это плохо.. я собсно всё это затеял ради не только автосортировки при последующих переключениях, но и для самопроверки на дубли или иные ошибки, потому как голова не компьютер.   Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 09:46 28-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: COUNTIF(A$1:A1;$A$1:$A$99))) в этой части ругается. Разве критерий можно ставить как массив? Не одну ячейку?   Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 12:08 28-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору bredonosec Цитата: попробовал, не понимает, как текст отображает. сменил на запятые - ничего не изменилось..   Эту часть не понял. Что именно вы сменили на запятые?   Как я уже написал. Эта формула отсортирует некорректно:   К примеру подобный список: 01-12 01-13 02-05 02-04 01-09 01-08 будет отсортирован так: 01-12 01-13 02-05 02-04 01-09 01-08 Цитата: в этой части ругается. Как именно ругается? Цитата: Разве критерий можно ставить как массив? Не одну ячейку? Такая конструкция обычно применяется, для извлечения уникальных значений из диапазона. Например MATCH(TRUE,(COUNTIF($B$1:B1,$A$2:$A$99=0),0)  в ячейке B2. При протяге вниз COUNTIF будет сравнивать массив с нулем и выдавать булевы значения TRUE или FALSE. Одинаковые значения в массиве в данном случае будут нулями. MATCH находит одинаковые значения и выдает только первое найденное.   Цитата: я собсно всё это затеял ради не только автосортировки при последующих переключениях, но и для самопроверки на дубли или иные ошибки А не проще ли просто применить подсветку дублей, или сортировка обязательна?     Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 12:40 28-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: или сортировка обязательна?   сортировка первична для контроля любых ошибок. Или дубли, или пропущенные, или как угодно спутанные.   ну и для легкости поиска, стоя у шкафа с бумажкой)) Цитата: Как именно ругается?   формула остается в виде формулы - "=iferror(index(..." вместо значения. И сохранить не дает, крича об ошибке. Если сменить на значение текущей строки - всё встает.   Но вместо выдачи позиции (как в предыдущей формуле, согласно ману) или значения отсортированным - дает значения, сортируемые неверно, примерно в стиле, описанном вами.     почему та предыдущая =COUNTIF($A$2:$A$8,"<"&A2)+COUNTIF($A$2:A2,A2) - глючит, не понимаю. Чего применить вместо - тоже.     Добавлено: нашел еще вариант https://exceljet.net/formula/sort-text-and-numbers-with-formula но... но внезапно что этот вариант, что старый стали превращаться в формулы вместо значений. Словно ошибочно всё.. Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 14:40 28-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: формула остается в виде формулы - "=iferror(index(..." вместо значения. И сохранить не дает, крича об ошибке. Если сменить на значение текущей строки - всё встает.   Странно. Точно все точки с запятыми сменили на запятые? Выложил на обменник, посмотрите. Цитата: нашел еще вариант https://exceljet.net/formula/sort-text-and-numbers-with-formula Этот вариант не будет работать с псевдочислами. COUNTIF их просто не видит. В общем, на данный момент рабочие только варианты с MMULT. Подумаю, как сделать вывод всех значений без исключений.   Добавлено: Такой вариант: Цитата: =TEXT(AGGREGATE(15,6,--SUBSTITUTE($A$2:$A$10,"-",),ROWS(D$2:D2)),"00-00") или так: Цитата: =TEXT(AGGREGATE(15,6,--SUBSTITUTE($A$2:$A$10,"-",),ROW(A1)),"00-00")   Принцип работы. Функция SUBSTITUTE($A$2:$A$10,"-",) убирает дефис, превращая 01-08, 01-09 в 108, 109 и т.д. Поскольку SUBSTITUTE функция для работы с текстом, то на выходе получается текст а не числа. Двойное отрицание (мат. операция) исправляет это. Затем числа в качестве аргументов передаются функции AGGREGATE, которая благодаря заданному параметру - 15, последовательно с помощью функции ROWS(D$2:D2) извлекает их в возрастающем порядке. Далее, все это добро на добровольной основе передается функции TEXT, которая формирует нужный для отображения формат "00-00".   Собственно, все. Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 15:22 28-07-2021 | Исправлено: Strogg, 10:47 29-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Кстати, в 365-ом офисе есть функция SORT которая без всяких танцев с бубном отсортировала бы данные в нужном порядке. Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 17:37 28-07-2021

bredonosec Platinum Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: Кстати, в 365-ом офисе   у нас разрешено пользоваться только строго тем списком софта, который указан дирекцией..   Ну и в онлайн (а это онлайн версия) сливать внутреннюю информацию строго запрещено...   а так я в курсе про это..         Добавлено: Цитата: помощью функции ROWS(D$2:D2) это имеется в виду тот же столбец, в котором формула? Всего записей: 16279 | Зарегистр. 13-02-2003 | Отправлено: 08:59 29-07-2021

Strogg Advanced Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Цитата: это имеется в виду тот же столбец, в котором формула? Да без разницы, это просто счетчик для формулы, который генерирует последовательность 1, 2, 3 и т.д. Обычно его помещают в тот же столбец, без опасений по поводу возникновения циклической ссылки, для удобочитаемости формул. Можно заменить на Цитата: =ROW(A1)   И еще массивный вариант, вводить через CTRL+SHIFT+ENTER: Цитата: =INDEX($A:$A,MOD(SMALL(IFERROR(--SUBSTITUTE($A$2:$A$10,"-",)*10^4+ROW($2:$10),),ROW(A1)+1),10^4)) Работает по тому же принципу, но другим методом. Каждая строка диапазона умножается на 10 000, затем функция MOD, где в качестве делителя указаны 10 000 возвращает номер строки {108000/8}, {108000/7} и т.д. Можно и без MOD c RIGHTB Цитата: =INDEX($A:$A,RIGHTB(SMALL(IFERROR(--SUBSTITUTE($A$2:$A$10,"-",)*10^4+ROW($2:$10),),ROW(A1)+1))) Здесь, думаю, разберетесь. Всего записей: 841 | Зарегистр. 08-05-2003 | Отправлено: 10:41 29-07-2021 | Исправлено: Strogg, 11:03 29-07-2021

Страницы: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

Компьютерный форум Ru.Board » Компьютеры » Программы » Microsoft Excel FAQ (часть 6)

Переход по форумам  Закладки   » Компьютеры Цифровое изображение Программы Microsoft Windows UNIX Другие OS Hardware В помощь сисадмину Прикладное программирование Игры Форумные игры   » Интернет Web-программирование В помощь вебмастеру Графика Хостинг Зацените-ка!   » Тематические Ikonboard v.2 Ikonboard v.3 Invision Board Другие форумы PHP-Nuke и другие порталы Мобила   » Общие Спорт Флейм Темы по региональным встречам Музыка и Кино Юмор Литература и Живопись   » Ru.Board Новости Помощь по форуму   » Андеграунд eBookz Варезник Раздачи и акции Мобильный варезник Андеграунд Игры и фильмы   » Специальные Тестирование