Ви коли-небудь зустрічалися з імпортованими негативними числами, знак мінус у яких стояв праворуч? SAP - це одна з програм, які проробляють таке з негативними числами: наприклад, 200 замість -200. Зміна формату вручну - це дуже нудна робота, але не обов'язково її робити вручну.
Припустимо, є довгий список чисел, тільки що імпортованих з іншої програми, і деякі з них є так званими негативними числами. Ваше завдання - перетворити їх на справжні негативні числа, які зможе розпізнати Excel. У цій вправі ми будемо використовувати діапазон А1: А100. В осередку В1 введіть наступну формулу:
= SUBSTITUTE (IF (RIGHT (TRIM (A1)) = "-"; RIGHT (TRIM (A1)) A1; A1); "-"; ""; 2) +0. в російській версії Excel = ПІДСТАВИТИ (ЯКЩО (ПРАВСИМВ (СЖПРОБЕЛИ (А1)) = "-"; ПРАВСИМВ (СЖПРОБЕЛИ (А1)) А1; А1); "-", ""; 2) +0.
Введіть її в таку кількість осередків цього стовпчика, скільки даних в стовпці А1, а потім скопіюйте введені формули і виділіть стовпець А1. Виберіть команду Спеціальна вставка → Значення (Paste Special → Values), щоб видалити формулу і залишити тільки значення. На рис. 6.16 показано, як список виглядав до застосування формули (діапазон А1: А7) і після.
Мал. 6.16. До і після переміщення знака мінус
Щоб зрозуміти, як працює ця формула, введіть наступну формулу в будь-яку клітинку. Осередок А1 повинна містити значення 200: = RIGHT (TRIM (A1); 1) A1. в російській версії Excel = ПРАВСИМВ (СЖПРОБЕЛИ (А1); 1) А1. Ви отримаєте результат -200.
Функція СЖПРОБЕЛИ (TRIM) просто гарантує, що в осередку немає символів пробілу. Отримавши значення -200, необхідно Видалити другий входження знака мінус. Це робить функція ПІДСТАВИТИ (SUBSTITUTE). Ви наказали їй замінити друге входження символу - на «» (порожній текст). В результаті повертається текстове значення, тому ви просто додаєте до нього 0, і Excel перетворює це значення в число.
Якщо вам необхідно часто виконувати цю роботу, потрібно записати спеціальний макрос, який спрощує її. Ось як це зробити. Макрос був оптимізований, щоб забезпечити максимальну швидкість виконання.
// Лістинг 6.2 Sub ConvertMirrorNegatives () Dim rCell As Range Dim rRange As Range Dim lCount As Long Dim lLoop As Long // Упевнитися, що дані виділені; якщо це не так, вивести _ // повідомлення і закінчити роботу макросу. If Selection.Cells.Count = 1 Then MsgBox "Виберіть діапазон для перетворення", vbInformation Exit Sub End If // Визначити змінну ТІЛЬКИ для текстових значень, наприклад 200 On Error Resume Next Set rRange = Selection.SpecialCells (xlCellTypeConstants, xlTextValues) / / Якщо змінна повертає Nothing, неправильних негативних чисел немає: // вивести повідомлення і закінчити роботу макросу If rRange Is Nothing Then MsgBox "Дзеркальних негативних чисел немає", vblnformation On Error GoTo 0 Exit Sub End If // Підрахувати, скільки осередків містять значення виду 200 і передати це чісло_ // в змінну для управління кількістю проходів циклу. lCount = WorksheetFunction.CountIfCSelection. "* -") // Встановити змінну на перший осередок виділеного діапазону Set rCell = Selection.Cells (1. 1) // Виконати цикл стільки раз, скільки неправильних негативних чисел For lLoop = 1 Те ICount // На кожному проході циклу встановити змінну на осередок, що містить * - // Зірочка - це символ шаблону Set rCell = rRange.Find (What: = "* -". After: = rCell. _ LookIn: = xlValues, LookAt: = xlPart. _ SearchOrder: = xlByRows. SearchDirection: = _ xlNext. MatchCase: = False) // Використовувати стандартний засіб Replace, щоб замістити знака "мінус" // порожнім текстом. Іншими словами, видалити його rCell.Replace What: = "-". Replacement: = "" // Помножити осередок на -1, щоб перетворити в негативне число rCell = rCell * -1 Next lLoop On Error GoTo 0 End Sub