AndreyMitrokhin

Ступор, небольшое замешательство, осознание, что чего-то пошло не так. Самые смелые заканчивают ввод формулы, видят нормальное значение, радуются, копируют формулу, после чего радость заканчивается. Потом, скорее всего, поиски в интернете "как избавиться от ПОЛУЧИТЬ.ДАННЫЕ...". Про то, как избавиться, мы ещё поговорим в самом конце. А пока...

В чём сила этой функции, брат?

Если вы будете смотреть различные видео по этой функции, почти во всех из них будут упоминаться диаграммы. Спорить не буду, у сводных диаграмм есть некоторые ограничения, но я, если честно, решил этот момент пропустить, так как разными путями эти ограничения можно обойти (если конкретно вам эта функция помогает именно при построении диаграмм, напишите в комментариях). Речь же пойдёт про другое, а именно, про перенос данных из сводной таблицы в обычный отчёт (таблицу). Вот тут, как мне кажется, функция раскрывает весь свой потенциал. Есть у нас данные, мы построили на основе этих данных сводную:

Предположим, что таблица постепенно наполняется. То есть сейчас есть данные до ноября, но потом будут и за декабрь. Источник лучше преобразовать в "умную" таблицу, так потом будет чуть проще обновлять сводную. Данные эти нам потом необходимо перенести в отчёт установленной формы:

Давайте сейчас определим сложности, с которыми мы столкнёмся, и которые не позволят нам банальным копированием-вставкой или прямой ссылкой заполнить наш отчёт:

1. Клиенты продавали не все наименования. Но, в теории, всё это у них может быть.

2. Порядок клиентов в сводной таблице и в отчёте разный ("потому что" ©).

3. Отчёт сразу за год, а у нас пока данные только до ноября. Добавлять формулы потом отдельно на декабрь не очень хочется. Хочется в начале года прописать формулу, потом сводную обновлять и радоваться жизни.

4. Почему не СУММЕСЛИМН? Потому что структура отчёта не позволяет суммировать продажи по наименованиям внутри каждого клиента. Или городить какую-нибудь формулу массива (не пробовал, но может и получится).

5. Можно ещё немного пофантазировать и предположить, что могут появиться новые клиенты и новые наименования. И решение должно это учитывать (добавил клиента с наименованиями, скопировал формулу, гордый собой пошёл пить чай/кофе).

И вот тут как раз на сцену выходит она - ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData).

Не смотря на всю свою несуразность и загадочность, начиная с названия и заканчивая синтаксисом, функция довольно простая:

Первый аргумент - поле из сводной, по которому мы производим вычисления. Далее - ЛЮБАЯ ячейка из сводной таблицы (обычно берут верхнюю левую). А потом идут пары: в каком поле что нужно найти.

Первоначально нужные элементы указываются в виде текста. Вот тут и начинается самое интересное. Ведь вместо текста можно указывать ссылки на ячейки (учитывая все закрепления, конечно же). То есть если возвращаться к нашему отчёту, формулу для наименований можно прописать следующим образом (показана формула для ячейки В3):

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1)

Исходник 2 - лист, на котором находится сама сводная таблица.

В ячейке хотим видеть значение из поля "Сумма, руб" нашей сводной. G1 - ссылка на ячейку сводной. А дальше те самые пары. В поле "Наименование" ищем значение из ячейки А3 (1), в "Клиент" ищем А2 (2), в "Месяцы(даты)" ищем В1 (3). Порядок пар роли не играет.

ВАЖНО! Названия полей (наименование, клиент, месяцы(даты)), тоже можно сделать в виде ссылок на ячейки, если таковые есть. А вот название поля данных, в котором происходит расчёт (сумма, руб) обязательно должно быть указано текстом. В любом случае, названия должны совпадать с теми, которые указаны в сводной таблице.

Если сейчас скопировать формулу на все месяцы, то в декабре будет ошибка #ССЫЛКА, так как нет такого элемента в сводной таблице. Чтобы убрать это непотребство, просто добавляем функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1);"")

Да, решение далеко от идеального. Хотя бы потому, что для клиентов придётся писать свою формулу, а для наименований свою. Скопировать всё разом на всю таблицу тоже не получится, потому что нужно будет менять ссылку с названием клиента. Но это всё делается за минуту. А преимущества такого подхода очевидны:

1. Если в нашей сводной что-то будет смещено, то формула не сломается, в отличие от прямой ссылки.

2. Когда будут данные за декабрь, мы их добавляем в нашу "умную" таблицу, обновляем сводную и готово.

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

Недостатки у такого подхода, конечно, тоже имеются:

1. Если фильтровать сводную или полностью перелопатить её структуру, то значения в формуле будут пересчитываться в соответствии с тем, что сейчас отображается в сводной. Таким образом, например, если поставим фильтр в сводной таблице на какие-то определённые наименования, а потом забудем фильтр очистить, то в отчёте суммы по этим наименованиям потеряем (ячейки будет пустыми, ошибок не будет, ведь мы приправили всё ЕСЛИОШИБКОЙ).

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

Заключение.

На этом, пожалуй, всё. Повторюсь ещё раз, я описал свой опыт. С моей субъективной колокольни, именно такое использование данной функции наиболее полезное и практичное. То, что потом на основе таких вот отчётов можно строить обычные диаграммы - это уже производное от главного, а именно от того, что основное преимущество данной функции - более менее безопасный и гибкий способ переносить данные из сводной таблицы в обычные таблицы.

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

P.S.

Алё, а отключить-то как?! На всякий случай напишу, как отключить автоматическое создание этой функции, когда ссылаемся на ячейки сводной (большинству она вот совсем не нужна). Всё просто:

1. Выбираем любую ячейку сводной.

2. Вкладка Анализ - группа Сводная таблица - раскрываем Параметры - снимаем галочку Создать GetPivotData.

3. Profit!!11

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

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

Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная - Число - в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.

И так, что же можно сделать.

Важно! Прежде чем пробовать какой-то из способов, убедитесь, что у вас в принципе правильные разделители указаны. Если система использует запятую как разделитель групп разрядов, а у вас точки везде, нужно просто через поиск и замену поменять точку на запятую (и наоборот, если в качестве разделителя система использует точку).

Способ 1. Использовать инструмент "Текст по столбцам" (text to columns) . Выделяем все "проклятые" ячейки, идём на вкладку Данные - Работа с данными - Текст по столбцам, и на самом первом шаге нажимаем Готово.

Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят "Да какого..?! Да почему!? Да что с этой программой не так?!". Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на... Барабанная дробь... ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).

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

1. В произвольную ячейку пишем единицу (1)

2. Копируем эту ячейку (Ctrl + C)

3. Выделяем диапазон с "кривыми" числами

4. По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)

5. В контекстном меню выбираем Специальная вставка (Paste special)

6. В следующем окне выбираем "Умножить". Если в таблице есть какое-то оформление, то ещё желательно выбрать "Значения", чтобы это самое оформление не слетело

7. Жмём Ок.

Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).

Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом "А что это такое тут у тебя формуле?". Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.

Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак "дорожные работы") с ошибкой, то программа сама предложит преобразовать текст в число:

То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать "Преобразовать в число", и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).

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

К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле "Найти", а заменить на пусто.

Всесильный Power Query.

Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ - это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор - кАзёл. И всё же :) Собрал все случаи, про которые писал. Дальше:

1. Желательно преобразовать таблицу в "умную" (не сделаете сами, всё равно потом Excel это сделает за вас)

2. Далее вкладка Данные - Получить и преобразовать данные - Из таблицы/диапазона

3. Если настройки PQ не меняли, то автоматически будет применён шаг "Изменённый тип", который всё и сделает

4. В PQ на вкладке Главная нажимаем Закрыть и загрузить

5. Получаем на новом листе "умную" таблицу с правильными форматами.

Итог.

На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно :) Когда какой использовать - дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.

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

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) - правая кнопка мыши - Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание - по горизонтали - по центру выделения:

К остальным заголовкам можно уже применить "Формат по образцу" (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 - "Правильная" таблица

Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто - не делайте трёхмерные таблицы. Плоская таблица - вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 - "Правильные" даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить "29 марта 2023 г.". Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что "тупой иксель" не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

1. / - слэш

2. . - точка

3. "-" - дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 - Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я - молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная - Стили - Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку "Таблица с заголовками" - Ок. Всего 4 (!!!) нажатия на кнопку мыши и готово. В этих наших интернетах это называется "умная" таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. "Умная" таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 - Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но...

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим "1". Выделяем весь диапазон, где хотим что-то закрашивать. Главная - Стили - Условное форматирование - Правила выделения ячеек - Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке "Заливка" выбираем нужные нам цвета. А вот самое интересное происходит на вкладке "Число". Выбираем "(все форматы)", в поле "Тип" прописываем следующий формат ";;;" (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет "1", но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с "умной" таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

Заключение

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Нам необходимо в отдельные столбцы разнести код и наименование.

Решение

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному - наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) - подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;" ";"")) - подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;" ";"*";E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

Финальный шаг:

ПСТР (MID) - как много в этом слове... А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 - понятно, ячейка с текстом.

D2+1 - начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 - количество извлекаемых символов. Порядковый номер звёздочки - порядковый номер первого пробела - 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

Заключение

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

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в "Должно быть":

Здесь уже расписывать не буду, а то ещё одна "простыня" получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать - это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Ссылка - (59) Полезные приёмы при работе в Excel. Часть 2. - YouTube

Текстовое число в нормальный числовой формат.

Данная тема была поднята в комментариях. Там я рассказал про один из способов решить этот вопрос. Но, пожалуй, повторюсь. Зачастую, при выгрузке из различных корпоративных систем, числовые данные выгружаются в Excel в формате текста. С этими псевдо-числами ты не можешь производить никаких вычислений (кроме подсчёта их количества). Простое присвоение этим данным числового формата не даёт нужного результат, они всё равно остаются текстом. Что нужно сделать. Выделяем "кривые" данные (можно сразу весь столбец), присваиваем нужный формат. Не снимая выделения с диапазона, переходим на вкладку Данные - Работа с данными - Текст по столбцам:

Нажимаем на неё и, не вдаваясь в подробности, сразу жмём "Готово". Почему так происходит, расскажу в видео. Дополнительно покажу ещё пару способов, как это можно сделать.

Изменение поведения маркера автозаполнения (квадратик справа внизу).

Ещё один момент, который был навеян комментарием (вот она, сила ИКСэль). Маркер автозаполнения - это волшебная вещь. Ввёл в ячейку цифру 1, в следующую цифру 2, выделяешь их, тянешь за правый нижний угол вниз левой кнопкой мыши, вуаля! Excel воспринимает этот как арифметическую прогрессию с шагом 1 и заполняет тебе номера по порядку. С датами тоже чудесно работает. Вводишь 01.01.2023, тянешь вниз, он все дни подряд тебе прописывает. Кто посмеет сказать после этого, что Excel не милашка? Но что, если тебе не нужна прогрессия или дни подряд? Ты хочешь, чтобы во всех ячейках были именно "1" и "2" или "01.01.2023". Да, кто-то скажет, что можно воспользоваться смарт-тэгом "Параметры автозаполнения" и выбрать нужный тебе вариант:

И будет абсолютно прав. Это сработает. Но вопрос был "а можно ли скопировать значения без помощи вот этого меню?". Можно.

1. Тянем за правый нижний угол нужно с зажатым Ctrl.

2. Тянем за правый нижний угол не левой, а ПРАВОЙ кнопкой кнопкой мыши. В этом случае, правда, без дополнительного действия не обойтись, потому что как только правую кнопку мыши отпустишь, появится меню, где нужно будет выбрать, а что ты хочешь.

Магия Ctrl + Enter и инструмента "Выделить группу ячеек"

Бывало ли у вас такое, что нужно изменить значение/формулу в определённом столбце в некоторых отфильтрованных ячейках? Казалось бы, всё просто. Ставишь нужные тебе фильтры, прописываешь необходимые исправления, потом просто за правый нижний угол тащишь вниз (или два раза по нему щёлкаешь левой кнопкой мыши). Но, к сожалению, когда речь заходит про отфильтрованный диапазон, Excel довольно часто ведёт себя непредсказуемо, а именно, даже в скрытые фильтром ячейки вносит исправленное значение. Чтобы этого избежать, нужно провернуть следующее. Устанавливаем нужные фильтры, выделяем ячейки, в которых хотим поменять формулу, нажимаем Ctrl+G. Попадаем в диалоговое окно "Переход" и выбираем там "Выделить":

Хочу отметить, что данная команда вообще способна на многое. Но нам сейчас от неё нужно одно единственное: только видимые ячейки:

Далее очень важный момент! Не нажимая ничего лишнего (не выделяйте никаких ячеек, не ставьте курсор в строку формул и т.д.), нажимаем на клавиатуре "=" (равно), прописываем нужную формулу, а потом второй важный момент: ввод формулы завершаем сочетанием клавиш Ctrl + Enter. Магия в действии. Кстати, если часто по работе с этим сталкиваетесь, то имеет смысл добавить команду выделения только видимых ячеек на панель быстрого доступа. Команда так и называется - Выделить видимые ячейки. Как добавлять абсолютно любую команду на панель быстрого доступа я вкратце рассказал в первой своей статье. В видео к данной статье ещё раз по этому моменту пройдусь.

Alt + цифры

Признавайтесь честно, кого раздражали цифры и буквы, которые появляются на панели быстрого доступа, когда ты нажимаешь клавишу Alt?

А на самом деле, как по мне, безумно полезная штука. Почему? Потому что благодаря этому, мы любую команду можем вызвать с помощью "горячих" клавиш (условно их так назовём, потому что по сути они таковыми не являются). В моём случае, чтобы выделить только видимые ячейки в диапазоне, я должен нажать Alt + 09. Если мне нужно поменять представление в таблице, я нажму Alt + 9, потом на клавиатуре стрелку вниз, всё, выбирай нужное тебе представление. Сначала будет непривычно. Но со временем, когда привыкнешь, уже не глядя будешь нажимать. А буквы помогут без помощи мыши добраться до любой команды на вкладках. Признаюсь честно, буквами я никогда не пользовался, но знаю, что есть пользователи, которые принципиально презирают работу мышкой и вообще почти всё делают с помощью клавиатуры. Что ж, каждому своё.

Заключение

Кто-то может сказать, что большинство инструментов, про которые я рассказал, не так уж и сильно работу ускоряют. Я не буду с этим спорить, потому что это правда. Но из таких вот мелочей и складывается весь наш рабочий день (да и жизнь в целом, чего уж). Сэкономил там пару секунд, там 10, там ещё чуть-чуть. В итоге, всё вместе, это даёт ощутимую экономию времени, которое ты можешь потратить на более приятные вещи (чтение постов на Пикабу, например).

На этом пока всё. Спасибо за уделённое время и внимание. Надеюсь, что-то пригодится вам в работе. Правда, если после первой статьи успехом для себя я считал помощь хотя бы одному человеку, то сейчас решил поднять планку до двух человек. Комментарии, само собой, приветствуются. Благодаря вам я вижу, что волнует пользователей, но про что во время занятий я банально не успеваю рассказать.