То можно обеспечить правильное сложение данных и при вставках новых строк в любом месте диапазона суммирования. Или конкретно для нашего случая

=СУММ(ИНДЕКС(А:А;2):ИНДЕКС(А:А;СТРОКА()-1)).

Здесь аргумент А:А указывает на то, что суммирование выполняется в столбце А, аргумент 2, – что область суммирования начинается со второй строки, аргумент СТРОКА()-1 – что область суммирования заканчивается предыдущей строкой относительно строки, где вычисляется сумма. На другие столбцы формула может быть распространена простым копированием. Аналогичным образом могут быть отредактированы и другие итоговые функции.

Очевидно, что описанные подходы могут быть использованы и в горизонтальных итогах.

4.17. Примеры “из жизни”

Табулирование функций.Популярная задача вычисления и построения функций “по точкам” очень легко решается средствами Excel. Положим, нам требуется вычислить и нанести на график две функции F(X)

Y=SIN(X) и Y1=SIN(X)*COS(5*X).

В столбце А на рис. 4.17-2 расположим последовательные значения Х с шагом 0,3 от 0 до 6,3, в столбце В – функцию Y(X), в С – Y1(X). Клеточные выражения для начального значения Х, находящегося в А2, будут таковы

B2=SIN(A2) и

C2=SIN(A2)*COS(5*A2).

Далее формулы копируются вниз до последнего значения Х. Само Х может быть сформировано мышью с помощью средств автозаполнения, однако удобнее описать Х как функцию некоторого шага изменения аргумента (клетка D2)

Х=Х+ШАГ или А3=A2+$D$2.

Это позволит в дальнейшем легко изменять шаг, не изменяя множества значений Х в столбце А (сейчас ШАГ=0,3). На рис. 4.17-3 изображен график обеих функций, созданный в Excel.

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

Положим (столбец В рис. 4.18-4), бригада рабочих изготовила некоторое число деталей. Нам нужно оценить их труд следующим образом в зависимости от объемов выработки:

– рабочие, попавшие в верхний 20%-й участок диапазона изготовленных деталей, получают оценку “отлично”,

– попавшие в последние 10%, – оценку “плохо”,

– остальные – оценку “нормально”.

Построим функцию, реализующую наши цели. Здесь воспользуемся рис. 4.17-5, где выработка (число деталей) обозначена буквой Х.

Нам нужно найти значения Хн и Хо, являющиеся рубежами диапазонов нормально и отлично.

DX=Хmax-Хmin, Хн=DX-10%*DX,

Хо=Xmin+20%*DX.

Тогда

Xн=(Хmax-Хmin)-10%*(Хmax-Хmin)

=(Хmax-Хmin)*(1-10%),

Xо=Хmin+20%*(Хmax-Хmin).

Отсюда клеточная функция для ячейки С2 будет выглядеть так

С2=ЕСЛИ(B2>(МАКС(B$2:B$5)-МИН(B$2:B$5))*90%;"отлично";

ЕСЛИ(B2>МИН(B$2:B$5)+20%*(МАКС(B$2:B$5)-МИН(B$2:B$5));"нормально";"плохо")).

 
 

 

 

  A B C D     A B C D
N Товар Единиц Цена   N Товар Единиц Цена
стол     стол  
стул   стул
шкаф   шкаф
тумба     тумба  
кровать   кровать
  Сумма       Сумма  
        Рис. 4.17-6        

Автонумерация.В ежедневной практике коммерческих организаций требуется формирование прайс-листов на продаваемую продукцию. Обычно, они меняются незначительно. Некоторые товары добавляются другие исчезают, как правило, не надолго. Скорее всего, позже они появятся снова. Организуем автоматическую нумерацию имеющихся товаров таким образом, чтобы последовательные номера присваивались только имеющимся в наличии товарам (количество единиц которых превышает 0). Это позволит поддерживать правильную нумерацию данных, не удаляя (и, следовательно, не вводя затем повторно) из таблицы временно отсутствующие товары. На рис. 4.17-6 слева показан исходный прайс-лист, а справа – он же, но при отсутствии некоторых товаров (столов, тумб).

  A B C D E
ДЕСЯТКИ и ЕДИНИЦЫ   СОТНИ
число текст   число текст
     
один   сто
два   двести
три   триста
четыре   четыреста
пять   пятьсот
шесть шестьсот
семь   семьсот
восемь   восемьсот
девять   девятьсот
    . . .      
девяносто девять      
           
    Рис. 4.18 -7    
  A B
Всего: 4 345 103,2      
Прописью: четыре млн. триста сорок пять тыс. сто три руб. 20 коп
    Рис. 4.17 -8    

Видим что строки, содержащие товары, которых нет в наличии, не нумеруются, но последовательность остальных номеров правильная. Здесь

A2=ЕСЛИ(C2>0;

СЧЁТЕСЛИ(C$1:C2;">0");""),

D7=СУММПРОИЗВ(C2:C6;D2:D6).

Далее прайс-лист должен быть отпечатан. Чтобы избежать вывода ненумерованных строк, следует воспользоваться операцией фильтрации (см. соответствующий раздел) – установить отбор такого множества данных, в которых отсутствуют строки, не содержащие информации в столбце № или столбце Единиц.

Сумма прописью.Построим полезную функцию преобразования суммы (до 9 999 999 руб.), представ­ленной числом, в “сумму прописью”.

 

Необходимость в этом возникает очень часто при формировании денежных доку­ментов. Для указанной цели в отдельный лист (или даже в отдельную книгу) вносятся (рис. 4.17-7) две таблицы. Таблица, связы­вающая числа от 0 до 99 с соответствую­щими числительными (десятки и единицы), и аналогичная таблица чисел от 0 до 9 (для сотен). С тем, чтобы не усложнять формируемую функцию необходимостью вставки пробелов между словами, все числительные в таблицах имеют по одному пробелу в конце текста. С целью упрощения формул введем для этих блоков имена: блоку А3:В102 присвоим имя ЕД (от слова единицы), блоку D3:E12 – СТ (от слова сотни). Использование имен позволяет также не указывать в формулах имена листов, где находятся поименованные блоки. На рис. 4.17-7 в клетке В1 представлено исходное число в цифровой форме, а в В2 – результат преобразования. Используемая функция и связи ее компонент с преобразуемым числом приведены на рис. 4.17-9. Она состоит из девяти текстовых составляющих, объединенных знаком сцепления символьных данных “&”. Первая строка преобразует один самый старший седьмой разряд (разряд миллионов) исходного числа с помощью данных, содержащихся в блоке ЕД. Вторая – вставляет слово “млн.” (если миллионы есть). Третья – один разряд сотен тысяч. Четвертая – два следующих разряда из блока ЕД. Пятая – включает слово ”тыс.”, если число содержит тысячи и т. д. В каждой из строк, отвечающей за какие-либо цифры (кроме копеек, которые не нужно преобразовывать в текст), применяется функция ВПР(). Однако чтобы воспользоваться ею, необходимо вы­полнить сложные действия по извле­чению нужных компонент числа, используемых в качестве аргументов функции ВПР(). Подробнее рассмотрим только первую строку. Здесь исходное число, находящееся в ячейке В1, сначала преобразуется в текстовую форму выражением ТЕКСТ(B1; "0000000,00"), чтобы затем выделить из числа отдельные разряды функцией ПСТР(…). В данном случае функция ПСТР(ТЕКСТ(B1; "0000000,00");1;1)извлекает из него фрагмент, начиная с первой позиции длиной в один символ (т.е. самую первую цифру числа). Далее эта цифра из текстовой формы снова обращается в числовую функцией ЗНАЧЕН(…), с тем, чтобы можно было подставить ее в функцию ВПР(…), разыскивающую цифру прописью в блоке ЕД. Таким образом, первая цифра числа 4 345 103,2 преобразуется в слово "четыре".

Замечание. Довольно скучно вводить числа прописью от 1 до 99 в блок ЕД. Это можно сделать довольно быстро, если воспользоваться средствами копирования и преобразования данных. Числительные от 1 до 19, конечно, придется ввести непосредственно. Далее следует ввести слово “двадцать ” и скопировать его в следующие девять строк. Затем “тридцать ” и сделать тоже самое. И так до “девяносто ” включительно. Теперь нужно присоединить к этим словам числительные “один”, “два”, … “девять”. Это удобно сделать так:

· Ввести в столбец С указанные числительные рядом с клетками, содержащими слово “двадцать ”, получив, например, такие строки смежных клеток двадцать один , двадцать два и т.п.

· Скопировать несколько раз весь блок числительных “один”, “два”, … “де­вять” вниз, чтобы они стали рядом со словами “тридцать ”, “сорок ” и т.д.

· Соединить в столбце D оба слова из столбцов В и С так, что бы двадцать один превратилось в двадцать один . Для этого нуж­но воспользоваться формулой вида D24=B24&C24, скопировав ее затем вниз до конца таблицы.

· Созданные таким образом данные переместить из столбца D на положенное место в столбец В, используя механизм переноса только значений (Правка+Специальная вставка+Вставить+Значения).