Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить …
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов …
При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . …
Разрешим ввод в столбец только неповторяющихся значений с использованием специального Выпадающего списка . Для этого необходимо динамически модифицировать Выпадающий список, последовательно исключая из него только что введенные значения.
Функция ЧСТРОК() , в английском варианте ROWS(), подсчитывает число строк в диапазоне ячеек или массиве констант. Например, формула ЧСТРОК(A1:F3) возвращает значение 3, т.к. в заданном диапазоне 3 строки: 1, 2, …
Подсчитаем количество выходных дней, содержащихся в диапазоне дат. Даты введены в ячейки листа. Выходными днями считаются суббота и воскресенье (праздники не учитываются).
Если в ячейке G1 имеется формула для суммирования значений в столбце D ( =СУММ(D2:D10) ), то при вставке нового столбца между А и D формула будет автоматически изменена на =СУММ(E2:E10) …
Создадим связанный список, аналогичный списку рассмотренному в статье Связанный список , но усложним задачу: в элементах выпадающего списка , от которого зависит связанный, будут содержаться двойные кавычки ".
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1*5). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы …
Задача функции СТОЛБЕЦ( ) , английский вариант COLUMN(), - возвращать номер столбца. Формула = СТОЛБЕЦ(B1) вернет 2, т.к. столбец B - второй столбец на листе.
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Критерии заданы с использованием подстановочных знаков . Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет …
Для поиска значения на пересечении строки и столбца требуется наличие таблицы специального вида: в строке заголовков и самом левом столбце должны быть неповторяющиеся значения.
Выведем значение из последней заполненной ячейки в списке. Список не должен содержать пропусков (пустых строк). Решение будет работать для числовых и текстовых списков.
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие …
Рассмотрим поиск чисел в списке с повторами. Задав в качестве критерия для поиска нужное значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а …
Найдем максимальное количество подряд идущих значений в столбце. Например, максимально число подряд идущих положительных значений или нечетных значений или значений равных определенному числу.
Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ("Лист1!B3") эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что …
Формулы массива могут возвращать как единственное значение, так и несколько значений. В первом случае для отображения результата потребуется одна ячейка, во втором – диапазон. В этой статье рассмотрим формулы массива, …
Функция АДРЕС() , английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула АДРЕС(2;3) возвращает значение $C$2 .
Определим, имеется ли в текстовой строке буквы из латиницы, цифры или ПРОПИСНЫЕ символы. Научимся определять наличие нежелательных символов одной формулой.
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию с учетом РЕгиСТРА. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для …
Для построения формул массива иногда используют числовую последовательность, например {1:2:3:4:5:6:7}, вводимую непосредственно в формулу. Эту последовательность можно сформировать вручную, введя константу массива , или с использованием функций, например СТРОКА() . …
Подсчитаем количество рабочих дней между двумя датами в случае нестандартной рабочей недели: в случае четырехдневной недели или когда выходные дни - воскресенье и среда. При подсчете учтем праздники.
Найдем числовые значения, равные заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот …
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ( ) , английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.