Перейти из форума на сайт.

НовостиФайловые архивы
ПоискАктивные темыТоп лист
ПравилаКто в on-line?
Вход Забыли пароль? Первый раз на этом сайте? Регистрация
Компьютерный форум Ru.Board » Компьютеры » Программы » Microsoft Excel FAQ (часть 6)

Модерирует : gyra, Maz

 Версия для печати • ПодписатьсяДобавить в закладки
Страницы: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Открыть новую тему     Написать ответ в эту тему

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

  • Всего записей: 37397 | Зарегистр. 26-02-2002 | Отправлено: 08:02 27-03-2018 | Исправлено: AlexDAT, 03:25 03-09-2020
    SFC



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

    Всего записей: 1629 | Зарегистр. 21-01-2003 | Отправлено: 14:00 15-06-2021
    LaCastet



    Silver Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
    SFC

    Цитата:
    Мысли есть ?

    Возможно, запуском другой Excel, не из MS Office 2019 открывается.

    ----------
    Пиво — это еще одно доказательство того, что Господь любит нас и хочет, чтоб мы были счастливы!

    Всего записей: 4256 | Зарегистр. 27-07-2005 | Отправлено: 16:32 15-06-2021
    masgak



    Silver Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
    del

    Всего записей: 2712 | Зарегистр. 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 место.  
    пробовал менять нули на апостроф - та же пурга. Без ничего - ёксель радостно конвертит числа в даты..  
     
    как его победить?

    Всего записей: 15996 | Зарегистр. 13-02-2003 | Отправлено: 16:09 19-07-2021
    ALeXkRU



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
    bredonosec
    Цитата:
    вперемешку с текстом или пустыми (прочерками) - формулу сортировки текста  
    countif(C:C,"<"&C3)+countif(C$3:C3,C3)  
    первые нули подвешивают.  

      пример файла бы.. чтобы видно, что такое
    Цитата:
    вперемешку с текстом или пустыми (прочерками)
    и что не так, и как надо..
    Чтобы не гадать..
     

    Цитата:
    ёксель радостно конвертит числа в даты..  

    формат ячеек - текстовый же?

    Всего записей: 11170 | Зарегистр. 03-12-2003 | Отправлено: 17:35 19-07-2021
    bredonosec



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

    Цитата:
    формат ячеек - текстовый же?

    разумеется. Возможно, при групповой замене он его меняет, но визуально это не видно.  
     

    Цитата:
     пример файла бы.. чтобы видно, что такое  

    https://www.file-upload.com/3qgrfxcpa0aj
    вот кусок вырезал.  

    Цитата:
    и что не так, и как надо..
    дык -
    Цитата:
    В смысле, всем рисует 1 место.  


    Всего записей: 15996 | Зарегистр. 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)));"")&""
    У меня в качестве разделителя точка с запятой, замените на запятые.

    Всего записей: 839 | Зарегистр. 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);

     
    Я что-то логику этой формулы не улавливаю.. Берется функция смолл, игнорить ошибки, массив столбец А.. а дальше не понял. Как мы массив делим? Или что это за операция?
    И перечисление функций через запятую не понял..

    Всего записей: 15996 | Зарегистр. 13-02-2003 | Отправлено: 15:13 27-07-2021
    ALeXkRU



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
    bredonosec
    Цитата:
    перечисление функций через запятую не понял..  

      имхо, это просто: разделитель аргументов в функциях в рус.локали - ; а в eng - запятая
    (т.е. чтобы перевести
    Цитата:
    countif(C:C,"<"&C3)+countif(C$3:C3,C3)  
    на рус.эксель, нужно не только функции заменить на русские, но и запятые на точку с запятой)

    Всего записей: 11170 | Зарегистр. 03-12-2003 | Отправлено: 17:11 27-07-2021 | Исправлено: ALeXkRU, 17:12 27-07-2021
    bredonosec



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

    Цитата:
     имхо, это просто: разделитель аргументов

    я не об этом. Я о том, как перевести смысл выражения Strogg.
     
     
    Цитата:
    на рус.эксель, нужно

    у меня английский, это неактуально  
    Актуально понять формулу ))

    Всего записей: 15996 | Зарегистр. 13-02-2003 | Отправлено: 18:37 27-07-2021
    ALeXkRU



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

    Цитата:
    Я о том, как перевести смысл выражения  

    хмм.. у него ж там нет про запятые...  
    Цитата:
    перечисление функций через запятую не понял..  

    Всего записей: 11170 | Зарегистр. 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, то будет выведено лишь одно.
     
     
     

    Всего записей: 839 | Зарегистр. 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)));"")&""  

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

    Цитата:
    Дубли, то бишь одинаковые значения будут игнорироваться.  

    хм.. а вот это плохо.. я собсно всё это затеял ради не только автосортировки при последующих переключениях, но и для самопроверки на дубли или иные ошибки, потому как голова не компьютер.  

    Всего записей: 15996 | Зарегистр. 13-02-2003 | Отправлено: 09:46 28-07-2021
    bredonosec



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

    Цитата:
    COUNTIF(A$1:A1;$A$1:$A$99)))

    в этой части ругается. Разве критерий можно ставить как массив? Не одну ячейку?  

    Всего записей: 15996 | Зарегистр. 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 находит одинаковые значения и выдает только первое найденное.  

    Цитата:
    я собсно всё это затеял ради не только автосортировки при последующих переключениях, но и для самопроверки на дубли или иные ошибки

    А не проще ли просто применить подсветку дублей, или сортировка обязательна?
     
     

    Всего записей: 839 | Зарегистр. 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
    но... но внезапно что этот вариант, что старый стали превращаться в формулы вместо значений. Словно ошибочно всё..

    Всего записей: 15996 | Зарегистр. 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".  
    Собственно, все.

    Всего записей: 839 | Зарегистр. 08-05-2003 | Отправлено: 15:22 28-07-2021 | Исправлено: Strogg, 10:47 29-07-2021
    Strogg



    Advanced Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
    Кстати, в 365-ом офисе есть функция SORT которая без всяких танцев с бубном отсортировала бы данные в нужном порядке.

    Всего записей: 839 | Зарегистр. 08-05-2003 | Отправлено: 17:37 28-07-2021
    bredonosec



    Platinum Member
    Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

    Цитата:
    Кстати, в 365-ом офисе  

    у нас разрешено пользоваться только строго тем списком софта, который указан дирекцией..  
    Ну и в онлайн (а это онлайн версия) сливать внутреннюю информацию строго запрещено...  
    а так я в курсе про это..  
     
     
     
    Добавлено:

    Цитата:
    помощью функции ROWS(D$2:D2)

    это имеется в виду тот же столбец, в котором формула?

    Всего записей: 15996 | Зарегистр. 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)))

    Здесь, думаю, разберетесь.

    Всего записей: 839 | Зарегистр. 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

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


    Реклама на форуме Ru.Board.

    Powered by Ikonboard "v2.1.7b" © 2000 Ikonboard.com
    Modified by Ru.Board
    © Ru.Board 2000-2020

    BitCoin: 1NGG1chHtUvrtEqjeerQCKDMUi6S6CG4iC

    Рейтинг.ru