Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу.
Как рассчитать количество вхождений текста A в текст B. Например, в тексте «Автоматическая система парковки у новых автомобилей» – два вхождения текста «Авто».
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
ДЛСТР (LEN) вычисляет длину текста, единственный аргумент – текст. Пример: ДЛСТР ("машина") = 6.
ПОДСТАВИТЬ (SUBSTITUTE) заменяет в текстовой строке определенный текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст).
Пример: ПОДСТАВИТЬ ("автомобиль";"авто";«»)= «мобиль».
ПРОПИСН (UPPER) заменяет все символы в строке на прописные. Единственный аргумент – текст. Пример: ПРОПИСН ("машина") = «МАШИНА». Эта функция понадобится нам, чтобы делать поиск без учета регистра. Ведь ПРОПИСН("машина")=ПРОПИСН("Машина").
Чтобы найти вхождение определенной текстовой строки в другую, нужно удалить все ее вхождения в исходную и сравнить длину полученной строки с исходной:
ДЛСТР("Автоматическая система парковки у новых автомобилей") ДЛСТР("матическая система парковки у новых мобилей") = 8
А затем разделить эту разницу на длину той строки, которую мы искали:
8 / ДЛСТР ("авто") = 2
Именно два раза строка «авто» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» – тот, число вхождений которого нас интересует):
=(ДЛСТР(текст) ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый); ")))/ДЛСТР(искомый)
Как найти дубликаты в списке?
Выделяем список, выбираем на ленте в разделе «Главная»: Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.
В появившемся окне выбираем стиль выделения дубликатов.
Если же дубликаты нужно удалить, а не выделить, то выделите таблицу (в данном примере дубликаты предварительно выделены) и нажмите на кнопку «Удалить дубликаты» в разделе «Данные». В появившемся окне укажите, в каком именно столбце ищем дубликаты:
Обновляемые дата и время в Excel
Вводим формулы
=СЕГОДНЯ() или =ТДАТА().
Первая – текущая дата, вторая – дата и время. ТДАТА можно отформатировать как время, и будет отображаться только оно:
Значения обновляются при любом действии (вводе данных в любую ячейку).
Для Google Таблиц:
=NOW() текущие дата и время (отображение будет зависеть от форматирования):
Как определить номер недели по дате?
Чтобы определить номер недели по дате, которая находится в ячейке A1, введите следующую формулу:
=НОМНЕДЕЛИ(A1;2)
Второй аргумент, равный 2, означает, что первый день недели – понедельник. Если бы он был равен 1, первым днем считалось бы воскресенье.
Если у вас Excel 2003, воспользуйтесь следующей формулой:
=1+ЦЕЛОЕ((A1-ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;5)+ДЕНЬНЕД(ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;3)))/7)
В Google Таблицах тоже есть функция WEEKNUM. Просто укажите в качестве аргумента этой функции ячейку с датой, и получите номер недели:
= WEEKNUM(A1)
Как выделить даты, которые уже прошли?
Выделяем ячейки с датами, выбираем «Условное форматирование» → «Правила выделения ячеек» → «Дата»:
Выбираем подходящий вариант, нажимаем ОК и получаем результат:
Как быстро добавить новые данные в диаграмму?
Возможно, вам приходится обновлять некоторые диаграммы раз в месяц, квартал или с другой периодичностью. В примере вам нужно добавить данные за ноябрь и декабрь в диаграмму:
Как сделать это максимально быстро?
Очень просто: выделите те данные, которые нужно добавить (в данном случае диапазон A12:B13), скопируйте их (Ctrl + C), выделите диаграмму мышкой и вставьте (Ctrl + V) данные. Несколько секунд – и готово!
Используйте «Умные таблицы» Excel
Казалось бы, любой набор данных в Excel – это таблица. Во всяком случае, мы воспринимаем это именно так. Но для Excel это просто диапазон, который можно форматировать как таблицу нажатием кнопки на ленте инструментов, что делает работу ощутимо удобнее.
В выпадающем меню достаточно выбрать подходящий стиль оформления таблицы, и ваш диапазон будет отформатирован соответствующим образом.
Что изменится?
Во-первых, теперь формулы станут читаться легче, в них будут отображаться названия столбцов, а не адреса ячеек:
Во-вторых, при добавлении новых данных формулы будут протягиваться автоматически.
В-третьих, у таблицы можно добавить строку итогов – и в любой момент увидеть статистику, например среднюю цену или общую стоимость. Для этого надо выделить любую ячейку таблицы и отметить в разделе «Конструктор» пункт «Строка итогов».
