Хитрости и советы: Microsoft Excel

Часть 1

1. Способы подсчета числа ячеек.
2. Суммирование и подсчет числа значений с использованием нескольких критериев.

Часть 2

1. Как из нескольких xls-файлов сделать одну книгу Excel.
2. Как правильно очистить ячейку от содержимого.
3. Создание собственных функций рабочего листа средствами VBA.

Смотрите также хитрости и советы: Delphi & Microsoft Excel.

1. Способы подсчета числа ячеек.

В Microsoft Excel существует несколько функций, предназначенных для подсчета числа ячеек в области, удовлетворяющих тем или иным критериям:

• ДСЧЕТ. Данные должны быть загружены в таблицу, требуется отдельная область, содержащая критерии;
• СЧЕТ. Простой подсчет числа ячеек в области, содержащих числовые значения;
• СЧЕТЗ. Подсчитывает число непустых ячеек в области;
• СЧИТАТЬПУСТОТЫ. Подсчитывает число пустых ячеек в области;
• СЧЕТЕСЛИ. Очень гибкая, но ее возможностей часто недостаточно;
• Формула массива. Полезна, когда остальные способы не работают.

Несколько примеров формул, показывающих различные методы подсчета ячеек. Все формулы используют область с именем "data":

• число ячеек, содержащих отрицательные значения:
  =СЧЕТЕСЛИ(data;"<0")
• число ячеек, содержащих слово "да" (без учета регистра):
  =СЧЕТЕСЛИ(data;"да")
• число ячеек, содержащих текст:
  =СЧЕТЕСЛИ(data;"*")
• число ячеек, содержащих текст, начинающийся с буквы "к" (без учета регистра):
  =СЧЕТЕСЛИ(data;"к*")
• число ячеек, в которых есть буква "к" (без учета регистра):
  =СЧЕТЕСЛИ(data;"*к*")
• число ячеек, содержащих "Да" или "Нет" (без учета регистра):
  =СЧЕТЕСЛИ(data;"Да")+СЧЕТЕСЛИ(data;"Нет")
• число ячеек, содержащих текст длиной 7 символов:
  =СЧЕТЕСЛИ(data;"???????")
• число ячеек, содержащих значение от 1 до 20:
  =СЧЕТЕСЛИ(data;">=1")-СЧЕТЕСЛИ(data;">20")
• число ячеек, содержащих уникальные значения (текст игнорируется):
  =СУММ(ЕСЛИ(ЧАСТОТА(data;data)>0;1;0))
• число ячеек, содержащих ошибочное значение (это формула массива, введенная с помощью Ctrl+Shift+Enter):
  =СУММ(ЕСЛИ(ЕОШИБКА(data);1;0))

Любые из вышеприведенных формул могут быть использованы и в макросах VBA. Например, присвоение переменной NumWords числа ячеек в области с именем "data", содержащих текст длиной пять символов: NumWords = Application.COUNTIF(Sheets("Лист1").Range("data"), "?????").

2. Суммирование и подсчет числа значений с использованием нескольких критериев.

Этот совет содержит несколько примеров, которые должны решить большинство ваших проблем с суммированием и подсчетом числа значений. В отличие от ДСЧЕТ и БДСУММ, эти формулы не требуют использования отдельной области критериев.

Формулы примеров, представленные в этом совете, используют маленькую таблицу, представленную справа.

Использование одного критерия:

• сумма продаж в январе:
  =СУММЕСЛИ(A2:A10;"Январь";C2:C10)
• число продаж в январе:
  =СЧЕТЕСЛИ(A2:A10;"Январь")
• сумма продаж, где Месяц <> "Январь":
  =СУММЕСЛИ(A2:A10;"<>Январь";C2:C10)

Использование нескольких критериев:

• сумма продаж в январе и феврале:
  =СУММЕСЛИ(A2:A10;"Январь";C2:C10)+СУММЕСЛИ(A2:A10;"Февраль";C2:C10)
• сумма продаж за январь в филиале "Север". Для составного критерия в различных полях функция СУММЕСЛИ не работает. Однако вы можете использовать формулу массива. Введите формулу с использованием Ctrl+Shift+Enter:
  =СУММ((A2:A10="Январь")*(B2:B10="Север")*C2:C10)
• сумма продаж за январь во всех филиалах, кроме "Север". Также требуется формула массива:
  =СУММ((A2:A10="Январь")*(B2:B10<>"Север")*C2:C10)
• число продаж за январь в филиале "Север". Для составного критерия в различных полях функция СЧЕТЕСЛИ не работает. Используйте формулу массива:
  =СУММ((A2:A10="Январь")*(B2:B10="Север"))
• сумма продаж за январь, каждая продажа>=200. Требуется формула массива:
  =СУММ((A2:A10="Январь")*(C2:C10>=200)*(C2:C10))
• сумма продаж, где 300=<продажа<=400. Снова нужна формула массива:
  =СУММ((C2:C10>=300)*(C2:C10<=400)*(C2:C10))
• число продаж между 300 и 400. Формула массива:
  =СУММ((C2:C10>=300)*(C2:C10<=400))