Ответы пользователя по тегу Excel
  • Можно ли сделать список месяцев?

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

    Заводите, к примеру, в ячейку А1 дату "01.07.2015".
    В ячейку справа - B1 - вводите формулу
    =DATE(YEAR(A1);MONTH(A1)+1;DAY(A1))
    и протягиваете ее направо сколько нужно.

    Теперь выделяете весь этот диапазон, нажимаете Ctrl+1, чтобы перейти в формат ячейки, выбираете слева "Custom", справа в Type пишете "mm.yyyy"

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

    @ClearAirTurbulence
    Советую ТС использовать НЕ этот вариант, а вариант с regexp от entermix. Он элегантнее и гибче, хотя и может быть проблематичным, если мозг отказывается работать с регэкспами (как мой). Если с регэкспами беда, самому учить их лень\некогда, можно использовать следующий способ - с оговорками.

    ===

    Формально вашим критериям будет соответствовать код, который оставляет от скормленной ему строки только цифры, исходя из буквальной трактовки вашего ТЗ - включая фразу "и так далее".

    Однако, в телефонном номере некоторые знаки, не являющиеся цифрами, все же нужны. Это, например, "+" в первой позиции, иногда - скобки (хотя далеко не всегда).

    Закидываете код функции в любой доступный модуль (например, в модуль Personal.xlsb, если приходится работать часто), вводите функцию "=KeepNumbers(А1)", где А1 - адрес ячейки с "сырым" номером, получаете обработанный. ВНИМАНИЕ! Рекомендую прочитать комментарий ниже кода.

    Public Function KeepNumbers(InputString As String)
        Dim i As Long, newString As String
        
        For i = 1 To Len(InputString)
            If IsNumeric(Mid(InputString, i, 1)) Then
                Debug.Print True
                newString = newString & Mid(InputString, i, 1)
                Debug.Print newString
            ElseIf i = 1 And Mid(InputString, i, 1) = "+" Then
                newString = newString & Mid(InputString, i, 1)
            End If
        Next i
        KeepNumbers = newString
    End Function


    Данный код делает ТОЛЬКО следующее - оставляет плюс, если он есть в исходной ячейке первым символом исходного номера; удаляет все остальные символы кроме цифр. Его надо допиливать исходя из ваших полных требований.

    Этот код не приводит номера к нормальному виду (не делает коверсии "8910...->+7910", не проверяет тел. номер на корректность по длине, не проверяет правильность использования скобок - просто выбрасывает их). Если вам нужно приводить телефонные номера к нормальному виду, код необходимо усложнять соответствующими проверками.
    Ответ написан
  • Как в excel строки, содержащие текст "N", сделать последовательно?

    @ClearAirTurbulence
    Реализация может быть различной.
    Зависит, в частности, от того, сколько в таблице столбцов, в которых нужно искать слово - один или более; сколько искомых слов (например, если их много, то IF использовать будет неудобно, а то и невозможно). Предположим, что столбец один, и это столбец А, а искомых слов - два, Table и Street.

    Создайте дополнительный столбец B с формулой, например,
    =IF(IFERROR(SEARCH("Table";A1);0)>0;"Table";IF(IFERROR(SEARCH("Street";A1);0)>0;"Street";IF(IFERROR(SEARCH("Other";A1);0);3;"other")))

    Протяните ее вниз параллельно исходным данным. Тогда в столбец B будут выбраны ключевые слова Table\Street для каждой строки, и other, если строка не содержит ни одного ключевого значения.
    Теперь можно выделить столбцы A и B, и отсортировать по значениям столбца B.

    Если ключевых слов больше, то ввиду ограничения числа вложенности функции IF такой подход не пойдет. Тогда лучше использовать вариант с функцией CHOOSE:

    =CHOOSE(ISNUMBER(SEARCH("Table";A1))*1+ISNUMBER(SEARCH("street";A1))*2+ISNUMBER(SEARCH("keyword_n";A1))*3;"Table";"Street";"Keyword_n")


    Работает так же - протягиваете, сортируете по второму столбцу.

    Каждое новое ключевое слово добавляете к первому аргументу CHOOSE как элемент
    +ISNUMBER(SEARCH("keyword_n+1";A1))*[index+1]
    где keyword_n+1 - иcкомое ключевое слово, а [index+1] - следующий по порядку индекс. В итоге первый аргумент CHOOSE сводится к числовому значению, равному индексу искомого элемента*. В конце формулы идет перечень значений, выдаваемых по этому индексу. Новое ключевое слово как текстовую строку добавляете туда в конец.

    Внимание, в отличие от первого способа, при отсутствии в тексте строки ключевых слов выдает ошибку "#VALUE!" (вместо "other", как в предыдущем примере).

    * Корректно работает при условии, что в строке не могут встречаться более одного ключевого слова одновременно. Если у вас будет строка, где есть и Street, и Table, получится фигня. Это же касается и предыдущего способа.
    Ответ написан
  • Как в Excel проставить кавычки, круглые скобки массово?

    @ClearAirTurbulence
    Если вам скобки нужны чисто визуально, можно вместо функций просто присвоить нужным ячейкам кастомный формат, например, "(# ##0);(-# ##0);(@)". В приведенном примере любое число\строка, введенные в отформатированную им ячейку, будет отображаться в скобках. На знак числа это не влияет. Также можно работать с ним как с обычным числовым значением.

    Если нужно именно чтобы значение в ячейке имело скобки, то формат не подойдет.
    Если наличие других невидимых символов в результирующей строке неважно, также как и неважно то, что она становится действительно строкой, а не числом, можно использовать формулу (у меня Excel английский, не обессудьте)
    ="("&TEXT(A13;"#")&")"&CHAR(160)
    Формула добавляет после последней скобки неразрывный пробел, таким образом, результат превращается в текстовую строку с одиним "лишним" для нас, но невидимым символом. Любые математические операции, естественно, с ней будут невозможны без обратного преобразования в число. Но для таких случаев проще пользоваться именно форматированием, как я предложил выше.
    Ответ написан
  • Как сменить в win 8.1 в офисном пакете 2013 цвет рамки выделенной ячейки?

    @ClearAirTurbulence
    Не получится. Ни цвет рамки активной ячейки, ни заливка выделенного диапазона ячеек в 2013 не меняется. Можно поменять только цвет четки ячеек, но зачем...
    Ответ написан
  • Как сделать чтобы Email'ы сохранялись как простой текст (Libreoffice)?

    @ClearAirTurbulence
    Создал документ в LibreOffice\Calc (Версия: 4.4.2.2).

    Ввел текст из верхней картинки двумя способами - (1) с клавиатуры и (2) вставкой из буфера обмена.

    Сохранил файл ODS. Открыл файл. Все осталось в первозданном виде.

    Сохранил файл в XLS, XLSX. В обоих результрующих файлах форматирование поплыло, причем - по-разному. Ячейка, введенная из буфера обмена, пострадала в меньшей степени. Ячейка, введенная вручную, стала выглядеть, [почти] как у вас.

    Вывод: если вы хотите корректно работать с XLS, используйте MS Excel.
    Кстати, LibreOffice честно говорит о возможности потери форматирования при сохранения в XL*, так что и удивляться не стоило.

    Вариант решения, если эксель ставить нельзя по какой-либо причине:
    - использовать другие программы (может быть, другие свободные офисы позволят работать с документом корректнее, или google sheets)
    - "нормализовать" документ, храня разнородные данные в отдельных ячейках вместо одной, что, кстати, удобнее при последующей обработке данных
    - подумать об использовании настоящей базы даных, если таблица используется в ее качестве
    Ответ написан
  • Как пронумировать ячейки по содержимому?

    @ClearAirTurbulence
    Во-первых, задайте себе вопрос - а нужен ли здесь VBA?

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

    =IF(IFERROR(SEARCH("Новоалександровский";B1);0)>0;1;IF(IFERROR(SEARCH("Ставрополь";B1);0)>0;2;IF(IFERROR(SEARCH("Черкесск";B1);0);3;"-")))


    такой подход не сработает для множества проверок, т.к. ЕМНИП макс. вложенность - 7 IF, но если их мало, вполне прокатит. Если не хочется использовать IF, можно взять такую формулу:

    =CHOOSE(ISNUMBER(SEARCH("Новоалександровский";B1))*1+ISNUMBER(SEARCH("Ставрополь";B1))*2+ISNUMBER(SEARCH("Черкесск";B1))*3;1;2;3)


    Её можно более-менее нормально модифицировать и для большЕго списка проверок, главное, индексы не перепутать.

    2. Можно поступить еще элегантнее и использовать таблицу подстановок. Тогда менять нумерацию будет проще. Т.е. создать таблицу "Тест-строка-значение", и формулами lookup\database проверять значения в B1, и если нашлось искомое - подставлять "значение" из таблицы.

    3. Можно писать не программу на VBA (Sub), а функцию, и точно также использовать ее в таблице:

    Function CityCode(CityString As String)
      
    Select Case True
        Case CityString Like "*Новоалександровский*"
            CityCode = 1
        Case CityString Like "*Ставрополь*"
            CityCode = 2
        Case CityString Like "*Черкесск*"
            CityCode = 3
        Case Else
            CityCode = "N\A"
    End Select
        
    End Function


    Применение: в ячейке С1 пишете "=CityCode(B1)" и протягиваете вниз.

    4. Можно сделать и программу, но ее нужно будет вызывать каждый раз, так что функция, наверное, удобнее.

    Напоследок:
    - можно было использовать регулярные выражения, как-то так:
    www.macrostash.com/2011/10/08/simple-regular-expre...
    - оператор like в vba для поиска подстроки выглядит так: "строка-вход" like "*подстрока*"
    - выводить статусные мессаги в статусную строку занимательно, но незачем, т.к. туда все равно никто не смотрит, а если и посмотрит - при таких задачах вряд ли успеет прочитать
    - не очень понятно, зачем жестко задавать название листа, на котором работает ваш макрос. Обычно вполне хватает activesheet, а если пользователи - тормоза, то можно добавить проверку, на том ли листе они используют макрос, более надежными способами (например, проверкой по "опорным точкам" - заголовкам таблиц, неизменяемым значениям и т.п.)

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

    ===
    UPD
    ===

    Еще ключевой момент: если тестовых подстрок очень много и/или они тоже "пополняются", то, чтобы не переписывать макрос\функцию каждый раз, когда это происходит, логично иметь где-нибудь отдельный источник в формате "Тест-строка-значение", например
    Новоалександровский 1
    Ставрополь 2

    и парсить его при вызове.
    Таким образом добавить условие+индекс будет проще, чем лезть в редактор VBA и дописывать
    Case CityString Like "*Новое_значение*"
            CityCode = %новый_код%


    Вот здесь отличный пример использования array в функции (с ручной инициализацией значений, но все равно может пригодиться, например, как баз для последующей переработки): blog.mclaughlinsoftware.com/2010/05/28/udf-replace...

    ===
    UPD2
    ===

    Мой примерный код, естественно, нужно дорабатывать - например, cейчас он case-sensitive, от этого нужно избавляться, например с использованием LCASE\UCASE, навешивать туда всякие проверки и рюшечки.
    Ответ написан
  • Cfg файл в эксель, как преобразовать?

    @ClearAirTurbulence
    Для единичного файла - создаете пустой документ в экселе
    Идете в ленту DATA (Данные)
    В группе Get External Data (Получит внешние данные) кликаете кнопку From text (из текста)
    Вводите путь к файлу, ОК
    В первом диалоге убедитесь, что выбрано Delimited\с разделителями, Next\Далее
    Снимаем галку с табуляций, печатаем "=" в поле other\другой, Next\Далее, Finish\Готово
    Наименования секций типа [user] при необходимости можно убрать руками, или с помощью фильтров (например, отбросив строки, где у ячеек первого столбца есть символ "[", либо строки, где у ячеек второго столбца нет значений).

    Выделяем полученный массив, копируем
    Ищем свободное место (например, другой лист), Paste Special\специальная вставка, ставите галку transpose\транспонировать, ок
    Получаете param1-n как заголовки столбцов в ряду 1, value1-n под ними.

    Закидать их в одну общую таблицу можно вручную или хитрой магией (vba\формулы подстановки типа lookup\match). Если файлов меньше 50 и это только один раз, вручную будет быстрее*.

    * при условии, что все cfg идентичны по структуре, т.е. например в третьей строке у них всегда "Parameter 3", а в пятой - "Parameter 5".
    Ответ написан
  • Есть ли альтернатива Excel?

    @ClearAirTurbulence
    Ну знаете, если Эксель сложный для указанного функционала... то даже не знаю, что сказать. Да, фишка с автоформатом не всегда в тему, но легко обходится и имеет плюсы, кроме минусов (наверное, проще ввести 1-3 и нажать энтер, чем вводить "01.03.2015"?) - и она обходится, если надо ввести текст, похожий на дату - отформатируйте ячейки в формат "текст" до ввода данных / начинайте ввод данных с символа "`". Не совсем удобно, но не так уж часто это нужно.

    Есть еще Quattro Pro )
    Ответ написан
  • Как построить гистограмму в Excel 2010?

    @ClearAirTurbulence
    Если вы хотите получить аналог, вам нужно:

    1) отформатировать график (ширина столбцов, цвет линий разметки, и т.п.)
    2) поместить его не как график на отдельном листе, а как объект в таблицу (если он на отдельном листе)
    3) скрыть в таблице гридлайны, сделать мелкую квадратную сетку
    4) объединить нужные ячейки, чтобы текст в нужных местах показывать (либо поступить "чище" - не объединять, а использовать выравнивание по центру поверх нескольких ячеек, Center across selection)
    5) нужные значения получаем, ясное дело, формулами; тут надо понимать логику их расчета
    6) стрелочки вверх\вниз тоже элементарно получаются формулами, красятся в нужный цвет условным форматированием

    В итоге получаем бастарда, но вполне рабочего бастарда.
    Что касается конкретной реализации - это уже долгий, кропотливый труд )
    Ответ написан
  • Знает кто нибудь хороший учебник по Excel для продвинутого пользователя?

    @ClearAirTurbulence
    Еще могу посоветовать - после беглого прочтения подобных книг - искать решения конкретных задач. Скажем, нужно удалить дубликаты данных? Гуглите "excel remove duplicates", и вот оно, ваше счастье. Это существенно эффективнее, чем пытаться впихнуть в мозг невпихуемое.

    Ответы зачастую находятся на специализированных сайтах, где, кстати, можно узнать много интересного, если покликать по смежным темам.

    Вот несколько таких сайтов:

    exceltactics.com
    www.theexceladdict.com
    www.excel-easy.com

    Но главное - все-таки целевой поиск, это самое эффективное решение.
    Искать лучше на английском.
    Ответ написан
  • Как выбирать только нужные значения из таблицы?

    @ClearAirTurbulence
    Что-то похожее описано тут.
    Если не разберетесь - дайте знать, попробуем вместе адаптировать.
    Ответ написан
  • Стоит ли серьезно изучать VBA?

    @ClearAirTurbulence
    Изучать VBA целенаправленно следует только в том случае, если вы серьезно занимаетесь чем-то в MSO, преимущественно в Excel\Access, и реально чувствуете в нем потребность.

    В качестве "профессии" осваивать его не нужно, лучше взять что-нибудь посерьезнее.
    При необходимости что-то автоматизировать\написать какую-либо функцию\пригладить интерфейс в среде МSO, вы, зная любой другой язык программирования и имея google, относительно легко и быстро решите любую проблему в VBA, может, не так элегантно, как человек, который посвятл изучению VBA массу времени, но работать будет.
    Ответ написан