Ответы пользователя по тегу Excel
  • Как правильно сделать формулу в Exel максимальное значение с заголовком?

    @ClearAirTurbulence
    1. Ваша задача один-в-один рассмотрена здесь:
    https://excel2.ru/articles/adres-yacheyki-v-ms-exc...

    2. Стерка называется ластиком.

    3. Подумайте, что делать, если максимальных значений будет два или больше...
    Ответ написан
  • Как сделать чтобы в выделенную область вписывались ячейки из двух других областей?

    @ClearAirTurbulence
    формулами можно сделать так:
    =IF(ROW()>COUNTA(Sheet1!A:A)+COUNTA(Sheet1!B:B);"";IF(ROW()<=COUNTA(Sheet1!A:A);INDEX(Sheet1!A:A;ROW());INDEX(Sheet1!B:B;ROW()-COUNTA(Sheet1!A:A))))


    Если протянуть ее "с запасом", то при пополнении списков-источников совокупный список будет также пополняться.

    Пример:
    Спсиок 1 в столбце A
    Список 2 в столбце B
    Объединенный список в столбце F

    Результат:
    5c7d950caacfe405392163.png
    Ответ написан
  • Как сбросить настройки по умолчанию в excel 2010?

    @ClearAirTurbulence
    https://excel.tips.net/T002211_Setting_Default_Pri...

    Следуете рекоммендациям в статье, выставляете настройки, как вам надо (можно, например, отступы уменьшить, в общем, сравнить page setup на двух пк; или поставить печать на одну страницу по умолчанию, что, однако, может быть неудобно).
    Ответ написан
  • Как автоматизировать?

    @ClearAirTurbulence
    1. В любом свободном столбце справа от столбца "Название" во второй строке пропишите
    ="ул "&ПОДСТАВИТЬ(D2;" ул";"")

    * в моей формуле разделителями аргументов являются точки с запятой, но в зависимости от региональных стандартов в настройках Windows их может потребоваться заменить на другой разделитель, например, на запятую - обратите на это внимание, если формула не работает, как показано на примере ниже.

    2. Протяните формулу по всей высоте данных (проще всего в столбце D поставить курсор на нижней строке с данными, перейти в столбец, где во второй строке ввели формулу, зажать shift, зажать ctrl, нажать стрелку вверх, все отпустить, нажать Ctrl+D), но можно и мышкой, хотя замучаетесь, или копированием-вставкой.

    Выглядеть будет примерно так (в примере адреса ячеек другие, но суть та же):

    5c5dd647e0b07239919668.png

    В результате получим справа от столбца "чего-то ул" столбец "ул чего-то".

    3. Весь результирующий столбец копируем, вставляем на место исходного через "специальная вставка \ вставить значения" (чтобы вставились не формулы, а результирующие значения)

    4. Стираем ставший ненужным столбец справа от "Название".
    Ответ написан
  • Как в Excel произвести горизонтальную прокрутку колесиком?

    @ClearAirTurbulence
    1. Установите расширение OfficeScroll:
    https://github.com/T800G/OfficeScroll
    или
    free-sk.t-com.hr/T800soft/software/OfficeScroll.html

    2. Проголосуйте здесь, чтобы немного повысить вероятность внедрения этой фичи в следующих версиях программы.

    3. Если на мыши есть железный горизонтальный скролл (отклонением в сторону колеса прокрутки, либо в виде второго колеса прокрутки) можно использовать их.

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

    К примеру, в CorelDraw боковая прокрутка реализована через Ctrl+Scroll, а не через Shift.
    В Wordperfect Office X6 (более свежего нет под рукой) горизонтальная прокрутка через Shift тоже нигде не реализована.

    Чтобы было UX-единство, нужно было бы реализовать это на уровне Windows...
    Ответ написан
  • Как найти и удалить ячейки по значению в excel?

    @ClearAirTurbulence
    Автофильтр.
    Отфильтровать по "содержит" ключевое_слово.
    Выделить отфильтрованные строки.
    Удалить строки.
    Снять фильтр.
    Ответ написан
  • Как сделать Фильтрацию внутри таблицы excel?

    @ClearAirTurbulence
    Делаем строку-заголовок (Наименование | Цена).
    Включаем автофильтр.
    Кликаем на кнопку фильтра в столбце "Наименование", оставляем галку только на белом хлебе.
    Кликаем на кнопку цен, снимаем галку с самой низкой цены. Остаются только "неподходящие" строки, подлежащие удалению. Выделяем их, удаляем строки, снимаем фильтр.

    В принципе, легко автоматизируется, если нужно делать часто.
    Только перед этим желательно понять, действительно ли нужно это (стирание строк) автоматизировать, или лучше подойти к решению реальной задачи как-то иначе (выборка в отдельный файл, и т.п.).
    Ответ написан
  • Как в excel выводить строчки при значении ячейки меньше 30?

    @ClearAirTurbulence
    1. Тупо скопировать таблицу (если нужно, т.к. операция обратимая, все данные сохраняются), добавить автофильтр, в столбце "срок годности" поставить фильтр "меньше 30", вуаля
    как-то так
    5bf540d93aadf132288582.png

    2.Если нужно регулярно орбновлять, можно сделать через сводные таблицы (pivot), и при необходимости обновлять данные после изменения таблицы-источника
    как-то так
    5bf53f89ea453179200562.png

    Как вариант, сделать слайсер, и ручками в нем выбирать те сроки, что нужны. Таблица будет фильтроваться соответственно.
    как-то так
    5bf5408535b03740860310.png

    3. Можно сделать кнопочки и VBA, если к этому душа лежит, но ИМХО автофильтры проще всего и, вероятно, удобнее.
    Ответ написан
  • Как сгруппировать и подсчитать суму по названию товара?

    @ClearAirTurbulence
    Или через сводные таблицы,
    или, что проще, через автосуммы.
    Если таблица ненормальная, нормализуете, сортируете (удобнее всего через автофильтры) по наименованию товара, автосуммой задаете итоги при кажждой смене наименования товара.
    Ответ написан
  • Как быстро поменять строчки местам в Excel?

    @ClearAirTurbulence
    Скорее так:
    https://www.addictivetips.com/microsoft-office/how...
    TL;DR:
    Выделяете нужное (в вашем случае, строку, хотя можно и столбец, и ячейку), зажимаете и держите шифт, хватаете выделение за краешек (это важно!), двигаете мышь, отпускаете, когда падает туда, куда нужно. По ссылке есть даже анимация.
    Ответ написан
  • Как сделать определенные манипуляции в Excel 2007?

    @ClearAirTurbulence
    1.
    Как мне удалить все строки КРОМЕ ТЕХ в которых айди встречается только 2 раза?

    Вопрос сформулирован некорректно. Возможно, в виду имелось
    "Как мне удалить все строки КРОМЕ ТЕХ, айди которых встречается в таблице только 2 раза?"
    Иначе вопрос не имеет смысла.

    2.
    есть 4 колонки, так же с большим количеством записей. Как удалить все записи кроме тех, у которых в определенной колонке есть определенный текст?


    Автофильтр.
    Выделяете таблицу, или ставите в любоее ее место курсор, если она непрерывна. Включаете автофильтр. Выставляете условие "не содержит строку" и вводите строку, которую должны содержать оставляемые строки. Остаются только ненужные строки. Выделяете их. Удаляете. Снимаете фильтр. Отображаются ранее скрытые им строки, содержащие строку.
    Ответ написан
  • Как суммировать похожие диапазоны?

    @ClearAirTurbulence
    1. Нормализовать таблицу, т.е.:
    - убрать промежутки между группами
    - убрать заголовки групп, перенеся этот текст в каждую строку отдельного столбца:
    сейчас:

    Группа А
    x y z
    m n o

    Группа Б
    q w e
    r t y

    Надо:

    Группа А x y z
    Группа А m n o
    Группа Б q w e
    Группа Б r t y

    2. Далее по вкусу, варианты:
    - отдельная таблица с наименованиями групп и суммой по каждой с помощью sumif:

    Группа А sumif(диапазон_столбца_таблицы_с_данными_где_указана_Группа;"Группа А";диапазон_столбца_таблицы_с_данными_где_указано_то_что_нужно_суммировать)
    Группа Б sumif(диапазон_столбца_таблицы_с_данными_где_указана_Группа;"Группа Б";диапазон_столбца_таблицы_с_данными_где_указано_то_что_нужно_суммировать)

    - встроенный механизм экселя Subtotal - выделяете таблицу (группы должны идти подряд, т.е. если они вразнобой, ее нужно по этому столбцу сначала отсортировать), указываете, что суммировать, оно само добавляет подитоги по группам и общий итог

    - сводные таблицы
    Ответ написан
  • Настройка кратности ячейки?

    @ClearAirTurbulence
    1. Сначала определяете формулу проверки.
    Если, к примеру, первый референсный столбец, значение в ячейке А3, проверяемое значение в ячейке B3, то формула будет выглядеть так:
    =MOD(B3;A3)=0
    * В басурманском экселе эта формула извращена и выглядит следующим образом:
    =ОСТАТ(B3;A3)=0
    * В зависимости от региональных настроек, разделителем аргументов может являться как точка с запятой, так и запятая


    Если выдает TRUE, то всё ок:
    5abcbc2841344533356805.png

    Если FALSE, то нацело не делится:
    5abcbc31d8c59898769235.png

    2. Чтобы светилось красным - используем условное форматирование.


    5abcc1e189690565072460.png

    0) Ставите курсор на нужную ячейку (в примере здесь - ячейка B2)
    1) Ярлык на ленте Home \ Главная
    2) Кнопка на ленте Conditional formatting \ Условное форматирование
    3) Создаете новое правило

    5abcc2f9cb29a670067311.png

    4) В диалоге выбираете использование проверочной формулы
    5) вводите формулу (внимание, абсолютную адресацию ($) перед столбцом оставить, перед строкой - убрать); формула немного изменяется, т.к. подсвечиваем то, где она не соблюдается, и ставим абсолютную адресацию столбцов, чтобы не нарушалась при протягивании: =MOD($B2;$A2)<>0
    * Адреса могут меняться в зависимости от вашей таблицы
    6) Выбрать формат подсвечивания
    7) ОК

    8) Убедиться, что работает, через Управление правилами условного форматирования указать нужный диапазон применения:
    5abcbefc852d8812095023.png

    3. Чтобы не давало ввести - нужно использовать валидацию данных.


    5abcc0e271df9284027895.png

    0) Ставите курсор на нужную ячейку
    1) Ярлык на ленте Data \ Данные
    2) Кнопка на ленте Data validation \ Проверка данных
    3) Выставить Custom \ Другой в первом поле
    4) Ввести формулу во втором поле (в отличие от условного форматирования, со знаком равно перед нулем)
    4.5) На вкладке Error \ Сообщение об ошибке ставите
    галку
    5abcc1461397c694235506.png

    5) OK
    Ответ написан
  • Почему не ищет текст на странице?

    @ClearAirTurbulence
    Еще бывает, кроме того, что упомянул zamboga:
    1. [не ваш случай, судя по второму скрину] если текст не забит в ячейку, а формируется формулой, нужно в опциях поиска указать, чтобы искало не в формулах, а в значениях
    2. [не ваш случай, судя по скринам] Кроме "искать целиком" в опциях есть еще "match case", что тоже иногда влияет.
    3. Кроме того, иногда встречается некорректный текст, где, например, вместо латинской\русской "c\а\о" используется визуально аналогичный символ из другой раскладки
    4. Ваш случай - несовпадение строки. Если посмотреть на первый скрин:
    В таблице:
    "Supernova col.11"
    В поисковой строке
    "Supernova col. 11"
    Теперь найдите одно отличие.
    Ответ написан
  • Как заменить в excel или другом текстовом редакторе x-английский на х-русскую?

    @ClearAirTurbulence
    В Word'е это делается так:
    - включается поиск с wildcard'ами
    - забивается в поиск ([0-9])x([0-9]), где x - латинский икс
    - забивается в замену \1х\2, где х - ХЭ русское

    Чиатать так: заменить найденное "(цифра от нуля до девяти)икс латиницей(цифра от нуля до девяти)" на "(цифра от нуля до девяти, которую нашли до икса)хэ русское(цифра от нуля до девяти, что была после икса)"
    5a9b154a49fa2025198816.png
    В Excel'е так не выйдет, самое простое - делать через word.
    Если делать не в Word'е, в принципе не очень сложно будет сделать через VBA, но это надо писать макрос.
    Ответ написан
  • Как повысить контрастность выделения ячейки в exel?

    @ClearAirTurbulence
    Эта штука, о которой вы говорите, называется курсор.
    Поменять его цвет\толщину нельзя.
    Способ решения: напишите петицию на change.org в Микрософт. Серьезно, я (и многие другие) присоединятся. Потом ждите следующую версию офиса, где это внедрят, если наберете много сочуствующих.

    Я вас понимаю, особенно на hidpi мониторе тонкая зеленая рамка курсора еле видна.
    Есть костыльное решение, через макрос или add-in с макросом, который подсвечивает строку\столбец "крестом". Но оно не работает с фиксированным областями экрана (frozen pane) или разделенным окном (split pane), может приводить к вылету экселя (в редких случаях), и сбрасывает стек undo (а вот это реальная проблема, причем нерешаемая). В общем, не советую.

    Если таки соберетесь писать петицию, дайте знать в комментариях - присоединюсь.
    Ответ написан
  • Как составить ТОП в Excel?

    @ClearAirTurbulence
    Проще всего использовать фильтры.
    Как-то так
    59f4b8754380a159051529.png
    Ответ написан
  • Как объединить строки в excel?

    @ClearAirTurbulence
    1. Если позиций немного - тупо добавляете строку над\под группой строк, которую нужно "свести", вводите в одну ячейку формулу сложения всех нужных строк над\под ней, и протягиваете по горизонтали.
    Затем выделяете строку с суммами, копируете, вставляете как значения. Лишние теперь исходные строки удаляете.

    Как-то так
    59f28a17c4a89767404546.png


    2. Второй вариант потребует незначительных изменений таблицы - нужно будет скопировать месяцы непосредственно в строку над данными и использовать функцию Subtotal (Промежуточные итоги) (для каждого покупателя, если их несколько); при необходимости, опять же результат можно скопировать и вставить как значения, от лишиних строк - избавиться, либо скрыть:

    Как-то так
    59f28cfec4dd1145365919.png


    Есть другие пути, с помощью VBA, например, или с помощью Pivot, но тут надо смотреть - в зависимости от объема обрабатываемых данных, ручной подход, описанный выше, может оказаться проще и быстрее реализуем.
    Например, Pivot потребует некоторой нормализации исходной таблицы: для каждой строки нужно будет указать покупателя. Зато потом данные можно вертеть и крутить как угодно, редактируя настройки таблицы, или используя слайсеры, выделяя только интересующего\интересующих покупателей\товары\месяцы. Результат выглядит примерно так:

    Таблица
    59f28f9cb9df7886274135.png
    Ответ написан
  • Как запретить редактирование отдельных ячеек на листе таблицы?

    @ClearAirTurbulence
    У ячеек, которые защищать не нужно, следует в свойствах на последнем ярлычке "Protection" снять галку "Locked", или как это будет по-русски.
    bfe25ed609944c04a4824bb133f84bb2.png
    Ответ написан
  • Какие существуют методы защиты Excel файла?

    @ClearAirTurbulence
    Можно просто запаролить
    www.spreadsheet1.com/vba-password.html
    Но это ненадёжно, есть софт, который это обходит.

    И нужно учитывать еще вот это
    www.spreadsheet1.com/read-document-vba.html

    Можно обфусцировать код
    www.spreadsheet1.com/excel-vba-code-obfuscation.html

    Либо переписать код в VB6, VB.NET, C#.NET, VSTO, C++, Delphi.

    А есть и специализированные решения, но они не бесплатны
    www.spreadsheet1.com/unviewable-vba-project-app-fo...

    И еще к вопросу о копирайте, как можно его прописать в backstage
    www.spreadsheet1.com/how-to-build-a-custom-backsta...
    Ответ написан