Ответы пользователя по тегу Excel
  • Отбор значений в таблице?

    @ClearAirTurbulence
    Способов много. Как вариант - СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
    Т.е. в вашем примере в ячеке i8:
    =СУММЕСЛИ($E:$E, $h8, $d:$d)-СУММЕСЛИ($E:$E, $h8, $c:$c)

    Первое суммесли суммирует все ячейки в столбце D (приход), для которых значение в столбце E той же строки равняется значению ячейки, находящейся в столбце H слева от формулы. Второе суммесли делает то же, но суммируются ячейки из стобца c (расход). В результате суммируется весь приход по позиции, из него вычитается весь расход по позиции - насколько я понял, нужно именно это. Если нужно просто просуммировать приход или расход по критерию, просто возьмите первую или вторую половину формулы.

    Для строки 8 таблицы (№ п/п 7) вышеприведенная формула будет лежать в ячейке I8, критерий отбора брать из H8, соответственно; протянутая ниже в ячеку I9, формула будет брать критерий из H9, и т.д.
    Ответ написан
  • Как выделить доменное имя с ячейки Excel?

    @ClearAirTurbulence
    Формулами это будет выглядеть так:
    5158d09d411a41fe91a98c180f53e3ea.png

    где
    =SEARCH("^^";SUBSTITUTE(A1;".";"^^";LEN(A1)-LEN(SUBSTITUTE(A1;".";""))))

    - Поиск позиции самой последней точки в строке

    =RIGHT(A1;LEN(A1)-B1)
    - Выделение из адреса того, что справа от найденной точки
    Ответ написан
  • Как определить дату сегодня без года?

    @ClearAirTurbulence
    Надо сравнивать и месяц, и день, а не день с месяцем.
    Как-то так:
    =И(МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ());ДЕНЬ(A1)=ДЕНЬ(СЕГОДНЯ()))

    e060c51199334aec896fb8ab54036523.png
    Ответ написан
  • Как в Ворде при слиянии из Экселя сделать округление после запятой?

    @ClearAirTurbulence
    Вот два совета на такой случай:
    1 - указать формат в полях в Word
    In order to retain the two decimal places during mail merge, follow the instructions below:
    Press alt+f9 > Insert the field code \# "0.00" after the title of the merge field that needs to be limited to two digits after the decimal point.
    Example: {MERGEFIELD "Wages"\# "0.00"}
    Note: Press Alt+F9 to hide the merge field codes


    Если не поможет - В экселе создать столбец\строку, где данные будут сконвертированы в текст, и уже оттуда мерджить в Word
    You may need to turn you numbers into text
    Try this formula: =TEXT(A1,"0.00")
    Copy and Paste this as value and replace the orginal data (or simply have as a seperate field).
    Ответ написан
  • Как в LibreOffice Calc (ну или Excel) сделать цвет текста красным, если значение меньше 100%?

    @ClearAirTurbulence
    В экселе просто - Условное форматирование, создаете правило, где для ячеек со значением менее 1 - красный цвет.

    67330e56360a4cb7a8822f18d24c72eb.png
    Ответ написан
  • Как быстро сопоставить столбцы ссылок?

    @ClearAirTurbulence
    А зачем именно 10 последних символов? То, что они совпали, не дает гарантию, что не будет ложных совпадений.
    См. пример ниже, в котором есть два столбца с кучей совпадений и парой несовпадений.
    В 12 строке обоих спсиков 2 разных адреса, у которых последние 10 символов совпали, и они отловились как "совпадение". Чтобы этого избежать, нужно или увеличить подстроку, а еще лучше искать по строке целиком, как в столбце H. В этом столбце функция MATCH показывает индекс строки, полностью идентичной искомой, в указанном массиве.
    8ffaa44e3f1f49e889d79c40bfe34696.png
    Если есть вопросы - пишите сюда комменты.
    Ответ написан
  • Как вывести таблицу за исключением некоторых строк?

    @ClearAirTurbulence
    Как писали выше - через Автофильтр. Если хочется процесс автоматизировать (например, таблица с исключениями содержит много полей, обновлять которые руками в автофильтре замучаешься; таблица с исключениями часто меняется; и т.п.), то нужно добавить в основную таблицу столбец, в котором прописать формулу, проверяющую Артикул каждой строки на совпадением с артикулами из таблицы исключений (обычно делается через match\vlookup\index-match). Формула выдавала бы, к примеру, TRUE для исключений и FALSE\пусто для остальных строк. И тогда по такому столбцу делать автофильтр. Главное, не забывать обновлять его после изменений.

    А можно тупо избежать таблицы исключений - если она заполняется вручную, проще руками заполнять столбец "Исключения" непосредственно в основной таблице, если, конечно, там не много строчек с одинм артикулом, тогда лучше действительно по таблице проверять.

    На скриншоте указан слегка измененный пример: в нем показано, что даже если есть несколько строк с одним артикулом, все равно такой подход позволяет отфильтровать такие строки.

    6776fc76893e481292a93018c027155a.png
    Ответ написан
  • Как в Excel отфильтровать полужирный текст от обычного?

    @ClearAirTurbulence
    Штатно - никак. Самый простой способ - написать макрос, который либо пропишет в соседний столбец флаг для полужирных строк, либо поменяет у них цвет фона. И уже либо по флагу, либо по цвету фона фильтровать.

    UPD:
    Беглый гуглинг подсказал альтернативные способы, подробнее тут:
    trumpexcel.com/2015/02/filter-bold-font-formatting...
    примерно то же на русском, если надо
    https://support.microsoft.com/ru-ru/kb/213923
    forum.codenet.ru/q34804
    Ответ написан
  • Как в Excel брать данные для списка из другой книги?

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

    Недостаток: при запуске такого файла может отображаться диалог обновления ссылок, если таковы настройки Экселя.
    Ответ написан
  • Как в excel 2010 настроить горячие клавиши если такого пункта нету?

    @ClearAirTurbulence
    Единственный способ - создавать макросы с нужными операциями, и вешать на них горячие клавиши.
    Однако запуск макроса, ЕМНИП, сбрасывает историю Undo, что может быть крайне неудобно.
    В новых экселях с ленточным интерфейсом после нажатия клавиши Alt на ленте отображаются подскзки с клавишами, которыми можно активировать функцию с ленты. Но потребуется много времени, т.к. запомнить это для более-менее значимого набора команд нереально, а глядеть на подсказки неудобно, обновляются они медленно, и вводятся нестандартно - к примеру, для форматирования в формат "accounting" выделенных ячеек надо нажать Alt, отпустить егоб нажать последовательно H, A, N, затем еще и выбирать из выпадающего списка формат...
    Ответ написан
  • Как создать счета в Excel?

    @ClearAirTurbulence
    В экселе нет полей, там есть таблицы, состоящие из ячеек, если уж быть точным.

    Т.е., если конвертировать ваш вопрос в термины экселя, у вас есть таблица, в которой у вас n строк, и столбцы, в ячейках которых для каждой строки заносится идентификатор карты и объем заправки.

    Выбор карты через выпадающее меню в таблице делается через валидацию данных, где вы указываете диапазон, в ячейках которого перечислены карты (на примере - F2:F4).

    46ced286432842c6a06a56df25216cae.png
    В итоге получится что-то вроде такого:
    00d6afc507d14f7b8e6ec1cbb2ebdfeb.png
    Ответ написан
  • Как строку типа "17,946.91 MB" преобразовать в обычное число "17946,91"?

    @ClearAirTurbulence
    Надежнее и проще всего избегать макросов там, где это не нужно. Исходные данные вводим, к примеру, в столбец А, а в столбце B пишем формулу

    =VALUE(CLEAN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;" MB";"");",";"");".";","))))


    Если у вас богомерзкий русский Excel, судя по русскому разделителю десятичных, то она будет выглядеть так:

    =ЗНАЧЕН(ПЕЧСИМВ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;" MB";"");",";"");".";","))))


    9e8f6326a87647129aa4fb4c13a785b2.png

    (для ячейки B1, соответственно "сырое" значение в А1, затем протягивается на нужный диапазон)
    Ответ написан
  • Excel автозаполнение дат по 3 одинаковых?

    @ClearAirTurbulence
    Можно с помощью формул.
    Первые три ячейки заполняем вручную. С четвертой и ниже вводим формулу*:
    =IF(AND(A3=A2;A2=A1);A3+1;A3)
    Получаем желаемое. Да, формулу можно было бы сделать умнее и красивее, но и так работает, затраты на решение не должны быть неразумно высокими :)

    * если excel английский, если русский, формулу надо переводить, спасибо майкрософту!
    Ответ написан
  • Как правильно сделать диаграмму?

    @ClearAirTurbulence
    В таблице значение ввожу отрицательные, но в графике необходимо чтоб расходы отображались в положительных значениях

    Два способа:
    - строить график по вспомогательному столбцу (добавить в таблицу столбец с абсолютным значением расходов)
    - изменить форматирование оси:
    кликаете ПКМ на оси, выбирает "формат оси"\"format axis", дальше в появившейся справа панели - "Number"/"Число", в поле "Код формата" введите:*
    "#\ ##0.00\ "₽";#\ ##0.00\ "₽"
    В 2007 Excel'е то же самое, только вместо панели справа там откроется диалог.
    При условии, что порядок значений оси в настройках выставлен на обратный (см. сл. пункт).
    * ЕМНИП, если формат не работает, вместо ";" используйте установленный в системных свойствах разделитель
    и были вверх а не в низ

    Открыл у себя в Excel 2007/2016 - они вверх, т.к. у вас в свойствах оси прописано "обратный порядок значений"\"Values in reverse order"

    и само название "столбцов" было сверху

    В Excel 2016 это делается так:
    - кликаете ПКМ на подписи, выбирает "формат оси"\"format axis", дальше в появившейся справа панели - "Labels" (не знаю как в русском, наерное, "подписи"), "Label Position"\"Подписи оси" поставить вместо "Next to Axis"\"рядом с осью" "Low"\"внизу" (т.к. порядок значений вертикальной оси обратный, расположение также зеркалится). В 2007 Excel'е то же самое, только вместо панели справа там откроется диалог.

    Имхо Excel не лучший вариант для семейной бухгалтерии, хоть и довольно гибкий.
    Может быть, меет смысл смотреть в сторону чего-нибудь типа AbilityCash. Только сначала туториал посмотрите.
    Ответ написан
  • Как в Excel представить число в виде "Осталось XX дней, YY час, ZZ мин"?

    @ClearAirTurbulence
    Надо еще на 24 делить.

    =TEXT(C8/24;"dd HH:mm")
    * Внимание: формула Text в русскоязычном Excel - "ТЕКСТ"
    * В русскоязычном Excel для формата может иметь смысл использовать "дд ЧЧ:мм" в зависимости от региональных настроек; если файл будет использоваться на машинах с разными настройками, имеет смысл учесть это в формуле (определять язык, использовать соответствующую строку форматирования).

    Пример работы:
    вход / выход

    27,32456 / 01 03:19
    24,00000 / 01 00:00
    24,25000 / 01 00:15
    49,50000 / 02 01:30
    Ответ написан
  • Какой алгоритм решения уравнения граф способом в EXCEL?

    @ClearAirTurbulence
    Excel не очень приспособлен для построения графиков мат. функций.
    Если приперло и нужно именно в экселе, то это делается так:
    https://www.google.ru/search?q=excel%20quadratic%2...

    Гораздо проще использовать для этого специализированные инструменты:
    https://www.wolframalpha.com/input/?i=x%5E2%2B2x-6...

    А еще проще превратить перед этим x^2+2*x-6=3+2*x
    в x^2+2*x-6-(3+2*x)=0
    x^2-9=0
    https://www.wolframalpha.com/input/?i=x%5E2-9%3D0
    Ответ написан
  • Как реализовать изменение значения ячейки одного листа при изменении значений другой ячейки на другом листе?

    @ClearAirTurbulence
    Это делается через COUNTIF (счётесли).
    // скорректированный ответ //

    См. картинку. Рекомендую ее открыть отдельно, чтобы было лучше видно.

    ad0015842f46458ebd00bba03783dd7c.png

    Комментарии:

    1. Как лучше структурировать таблицу - вопрос открытый.
    Например, можно через валидацию в таблице "Люди" сделать так, чтобы можно было не вводить адрес руками или, что еще хуже, его номер (идентификатор, индекс по сути) - а выбирать его из удобного выпадающего списка, который населяется значениями из таблицы "адреса". О том, как это сделать, можно прочитать здесь.

    2. В таблице на приложенном скрине:

    В таблице "Адреса" у каждого адреса в столбце "Макс" стоит то самое максимальное число использований.

    В столбце "Использовано" через формулу СЧЁТЕСЛИ посчитано, сколько раз такой адрес использован в таблице "Люди" (по стобцу "Номер адреса" (столбец L), но ничто не мешает вообще отказаться от него и считать непосредственно по самому адресу (столбец K)).

    Столбец К "Адрес" заполняется через выпадающие списки, источником данных для которых являются адреса из таблицы "Адреса" (Столбец А). Делается через валидацию. Плюс в удобстве и невозможности ошибиться при вводе адреса.

    Столбец L "Номер адреса" заполняется по выбранному в предыдущем столбце адресу выборкой соответствующего номера из столбца B таблицы "Адреса". В принципе, можно обойтись и без него, и считать использование в столбце D не по номерам адресов, а пос самим адресам.

    Столбец M "Свободно для выбранного адреса" точно так же получается выборкой из таблицы "Адреса".
    Ответ написан
  • Формула для переноса слов при превышении заданной длины символов в ячейке excel?

    @ClearAirTurbulence
    Вообще ограничение странное, подумайте, нужно ли оно такое.
    Если все же да, тут два варианта, и оба с макросом.

    1) самый простой для реализации способ - вводить данные через макрос. Пишется простой макрос, который получает данные в inputbox, определяет длину, режет при необходимости, и пишет в чейку или в две. Макрос вешается на какое-нибудь удобное сочетание горячих клавиш. Не очень удобно.

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

    3) Самое правильное - снабдить таблицу vba кодом, который будет проверять данные сразу после ввода (например, через Worksheet_Change). И при необходимости делать то, что описано в п.2.

    При знании английского языка и смутном знании паскаля\бэйсика со школы и наличии гугла, непрограммистом (1) пишется за 5-15 минут, (2) за 0.5-3 часа, (3) за 0.5-4 часов с перерывами на кофе.
    Ответ написан