Магия Excel

2022-11-03 19:00:04 Давайте поговорим про даты в Excel (и в Google Таблицах основные принципы такие же).

Сегодня вот 44868 день - по летоисчислению Excel.
Потому что даты в Excel - это просто числа.

Что обеспечивает возможность расчетов с ними. Одна единица - это один день. А значит, чтобы получить дату через неделю, достаточно прибавить 7.
А если прибавить 0,5 ? Тогда мы получим дату со временем. Логично! Если единица - это день, то дробная часть - это время, часть дня. 0,5 - это полдень, 12:00:00.

На практике это значит:
- Если вы видите вместо дат числа (в районе 40-с-чем-то-тысяч, как правило) - просто поменяйте формат на "Дату". Со значениями все в порядке, вопрос форматирования (внешнего вида).
- Можно вычесть из даты число или прибавить - чтобы получить дату на соответствующее количество дней раньше или позже.
- Можно вычесть из одной даты другую - тогда мы получим число (количество дней между днями).
- Любое число (ну, кроме отрицательного) может стать датой, если поменять у него формат. Тут снова поможет изменение формата на "Числовой".

В Excel работают даты с 1 января 1900 года (на Mac с 1 января 1904 - но даты конвертируются при этом при открытии книг в разных системах). Если захотите составить табличку с историей чемпионатов мира по футболу - все получится. Но если вы занимаетесь историей в целом (или генеалогией) - видимо, будет маловато. Вы можете вводить более ранние даты, но с ними не будет работать магия (функции, которые предназначены для работы с датами, не будут выдавать правильный результат; проводить вычисления тоже не получится).

Вводить дату можно в разных форматах. Даже как число, а потом форматировать (если вдруг вы достигли просветления в Excel и помните, что 43132 - это 2 февраля 2018 года).
Но на практике речь о разных "нормальных" форматах:
ДД.ММ.ГГГГ (например, 01.06.2022)
ДД/ММ/ГГГГ (например, 01/06/2022)
ГГГГ-ММ-ДД (например, 2022-06-01)
ГГГГ/ММ/ДД (например, 2022/06/01)
ДД название месяца ГГ(ГГ) (например, 1 июнь 22)
ДД короткое обозначение месяца ГГ(ГГ) (например, 1 июн 2022)

Используете в формуле дату? Если она в ячейке - просто ссылайтесь на ячейку. Если хотите указать дату как константу прямо в формуле - возьмите ее в кавычки. Открыть/Комментировать

2022-10-31 13:45:00 Сочетания клавиш: выделяем таблицу "до упора" и возвращаемся к активной ячейке.

Думаю, многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки).
Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео.

Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится чуть менее часто используемое (ну, как нам кажется) сочетание — Ctrl + Backspace (⌃ + Delete). Открыть/Комментировать

2022-10-28 10:02:01 Так, а что делать без подписки Microsoft 365, если у вас одна из коробочных версий?

Шаманить со старыми текстовыми функциями - это первый вариант.

Можно найти положение некой "зацепки", например, пробела - с помощью функции НАЙТИ / FIND.
После этого определить, сколько символов нужно извлечь справа (это функция ПРАВСИМВ / RIGHT) из исходного текста - столько, сколько в нем есть (это вычисляется функцией ДЛСТР / LEN), минус положение пробела.
=ПРАВСИМВ(текст;ДЛСТР(текст)-НАЙТИ("символ-зацепка";текст))

Второй вариант - использовать мгновенное заполнение, о котором было чуть выше. Ввести одно-два названия в соседнем столбце (без ненужного слова "Корм" или других ненужных частей) и нажать Ctrl+E. Открыть/Комментировать

2022-10-28 10:01:54 ТЕКСТПОСЛЕ / TEXTAFTER - извлекать текст после какого-то знака/слова стало проще

Как же хорошо, когда появляются новые функции в Excel. Жаль только, что это счастье доступно подписчикам Microsoft 365, а в старых версиях такой функции нет.

Итак, берем ячейку с текстом (первый аргумент), указываем, после чего нужно извлечь текст (например, здесь после общего слова "корм" и пробела после него). Регистр учитывается.

Функция ТЕКСТДО / TEXTBEFORE тоже появилась. Открыть/Комментировать

2022-10-26 12:02:00 Мгновенное заполнение - один из самых простых и полезных инструментов Excel.

Поможет:
- извлечь из текста фрагмент (например, имя из ФИО)
- переделать текст в другой вид - например, вместо ФИО - инициалы плюс полная фамилия
- получить текст из нескольких столбцов (если у вас есть столбцы с датой и фамилией, например, а вам надо склеить это в одну фразу "День рождения такого-то человека такого-то числа.такого-то месяца"
- Поменять регистр текста

Задаем Excel 1-2 образца того, что надо получить - в пустом столбце. И либо ждем, что Excel сам предложит заполнить (как в начале видео) и нажимаем Enter (или щелкаем мышкой на серые значения), либо вводим одно значение и нажимаем Ctrl+E.


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

И магия работает в Excel 2013 и более новых версиях. Открыть/Комментировать

2022-10-25 18:31:25 А мы тут с Лемуром тем временем подписываем авторский договор на книгу с рабочим названием «Магия таблиц»

На фото над договором - одна из лучших книг по Excel (увы, пока не выходившая на русском языке) от мощнейшего автора Bill Jelen.

Про нее и другие табличные книги - тут:
https://teletype.in/@renat_shagabutdinov/excellent_books Открыть/Комментировать

2022-10-25 09:59:59 Как быстро построить диаграмму?
Alt + F1

(если хотите построить диаграмму на отдельном листе, то просто F11)

А также разбираемся, как быстро добавлять новые данные на диаграмму. Открыть/Комментировать

2022-10-25 09:37:18 Показываем, как отображать числа в тысячах с помощью пользовательского числового формата.

При этом сами числа никак не меняются, меняется только внешнее отображение (форматирование).

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

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

Для того, чтобы числа отображались в тысячах, нужно в формате после кода числа добавить пробел. Два пробела - и числа будут в миллионах. Три - в миллиардах, ну а далее - вы поняли ;)

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

При американских региональных настройках вместо пробела нужно будет использовать запятую. Открыть/Комментировать

2022-10-25 09:18:56 Привет! На связи кот ЛеМур и его помощник Ренат.

После того как мы обучили более тысячи человек на курсе МИФа "Магия Excel", а Лемур еще и успел поспать на паре десятков книг, посвященных таблицам (и начал писать свою собственную), решили, что пора создавать отдельный канал, на котором будем делиться советами, рассказывать про функции и инструменты.

Также напоминаем, что про Google Таблицы можно и нужно читать вот здесь, на канале, который Ренат ведет вместе со своим соавтором Евгением Намоконовым:
@google_sheets Открыть/Комментировать

2022-10-24 14:44:41 Channel photo updated Открыть/Комментировать