In diesem Artikel erfahren Sie, wie Sie ein Skript in Excel hinzufügen, das Ihnen bei der Analyse der Daten im aktiven Arbeitsblatt hilft. Hierbei handelt es sich um eine gute Übungsaufgabe, um Ihre Fähigkeiten mit Skripten zu verbessern.
Office Skript Übungsaufgabe in Excel: Analyse von IP-Adressen
In dieser Übung haben Sie die Aufgabe, Downloadberichte von der Website Ihres Unternehmens zu analysieren. Das Ziel dieser Analyse besteht darin, festzustellen, ob der Webverkehr aus den USA oder einem anderen Teil der Welt kommt.
Ihre Kollegen laden die Rohdaten in Ihre Arbeitsmappe hoch. Für den Datensatz jeder Woche gibt es ein eigenes Arbeitsblatt. Es gibt auch das Arbeitsblatt „Zusammenfassung“ mit einer Tabelle und einem Diagramm, das wöchentliche Trends zeigt.
Lösungsansatz
Sie schreiben ein Skript, das wöchentliche Download-Daten im aktiven Arbeitsblatt analysiert. Es analysiert die mit jedem Download verknüpfte IP-Adresse und ermittelt, ob der Download aus den USA stammt oder nicht. Die Antwort wird als boolescher Wert („TRUE“ oder „FALSE“) in das Arbeitsblatt eingefügt und auf diese Zellen wird eine bedingte Formatierung angewendet. Die Ergebnisse des IP-Adressstandorts werden im Arbeitsblatt summiert und in die Übersichtstabelle kopiert.
Schritt 1: Voraussetzungen
Bevor Sie beginnen, stellen Sie sicher, dass Sie die folgenden Anforderungen erfüllen:
- Eine funktionierende Installation von Microsoft Excel auf Ihrem Computer. (Excel für Windows (Version 2210 oder höher), Excel für Mac oder Excel im Web) Um Ihre aktuelle Version zu ermitteln, klicken Sie hier.
- OneDrive für Unternehmen.
- Internetverbindung.
- Jede kommerzielle oder Bildungslizenz für Microsoft 365 mit Zugriff auf Microsoft 365 Office-Desktopanwendungen, wie zum Beispiel:
- Microsoft 365 Business Standard
- Microsoft 365-Apps für Unternehmen
- Microsoft 365-Apps für Unternehmen (ehemals Office 365 ProPlus)
- Office 365 Business
- Office 365 Business Premium
- Office 365 ProPlus
- Office 365 ProPlus für Geräte
- Office 365 Enterprise E3
- Office 365 Enterprise E5
- Office 365 A3
- Office 365 A5
- Um Ihre aktuelle Lizenz zu ermitteln, klicken Sie hier.
Schritt 2: Vorlage
Laden Sie die Datei unten („Analyze-Web-Downloads“) auf Ihr Laufwerk herunter und öffnen Sie dann die Datei.
Schritt 3: Erstellen eines neuen Skripts
Öffnen Sie die Excel-Arbeitsmappe, klicken Sie im Menüband auf die Registerkarte „Automatisieren“ und wählen Sie „Neues Skript“ aus. Der Code-Editor erscheint rechts.
Schritt 4: Office-Skripteditor
Kopieren Sie den folgenden Code und fügen Sie ihn in den Skripteditor ein:
function main(workbook: ExcelScript.Workbook) {
/* Get the Summary worksheet and table.
* End the script early if either object is not in the workbook.
*/
let summaryWorksheet = workbook.getWorksheet("Summary");
if (!summaryWorksheet) {
console.log("The script expects a worksheet named \"Summary\". Please download the correct template and try again.");
return;
}
let summaryTable = summaryWorksheet.getTable("Table1");
if (!summaryTable) {
console.log("The script expects a summary table named \"Table1\". Please download the correct template and try again.");
return;
}
// Get the current worksheet.
let currentWorksheet = workbook.getActiveWorksheet();
if (currentWorksheet.getName().toLocaleLowerCase().indexOf("week") !== 0) {
console.log("Please switch worksheet to one of the weekly data sheets and try again.")
return;
}
// Get the values of the active range of the active worksheet.
let logRange = currentWorksheet.getUsedRange();
if (logRange.getColumnCount() !== 8) {
console.log(`Verify that you are on the correct worksheet. Either the week's data has been already processed or the content is incorrect. The following columns are expected: ${[
"Time Stamp", "IP Address", "kilobytes", "user agent code", "milliseconds", "Request", "Results", "Referrer"
]}`);
return;
}
// Get the range that will contain TRUE/FALSE if the IP address is from the United States (US).
let isUSColumn = logRange
.getLastColumn()
.getOffsetRange(0, 1);
// Get the values of all the US IP addresses.
let ipRange = workbook.getWorksheet("USIPAddresses").getUsedRange();
let ipRangeValues = ipRange.getValues() as number[][];
let logRangeValues = logRange.getValues() as string[][];
// Remove the first row.
let topRow = logRangeValues.shift();
console.log(`Analyzing ${logRangeValues.length} entries.`);
// Create a new array to contain the boolean representing if this is a US IP address.
let newCol: (boolean | string)[][] = [];
// Go through each row in worksheet and add Boolean.
for (let i = 0; i < logRangeValues.length; i++) {
let curRowIP = logRangeValues[i][1];
if (findIP(ipRangeValues, ipAddressToInteger(curRowIP)) > 0) {
newCol.push([true]);
} else {
newCol.push([false]);
}
}
// Remove the empty column header and add proper heading.
newCol = [["Is US IP"], ...newCol];
// Write the result to the spreadsheet.
console.log(`Adding column to indicate whether IP belongs to US region or not at address: ${isUSColumn.getAddress()}`);
console.log(newCol.length);
console.log(newCol);
isUSColumn.setValues(newCol);
// Call the local function to add summary data to the worksheet.
addSummaryData();
// Call the local function to apply conditional formatting.
applyConditionalFormatting(isUSColumn);
// Autofit columns.
currentWorksheet.getUsedRange().getFormat().autofitColumns();
// Get the calculated summary data.
let summaryRangeValues = currentWorksheet.getRange("J2:M2").getValues();
// Add the corresponding row to the summary table.
summaryTable.addRow(null, summaryRangeValues[0]);
console.log("Complete.");
return;
/**
* A function to add summary data on the worksheet.
*/
function addSummaryData() {
// Add a summary row and table.
let summaryHeader = [["Year", "Week", "US", "Other"]];
let countTrueFormula =
"=COUNTIF(" + isUSColumn.getAddress() + ', "=TRUE")/' + (newCol.length - 1);
let countFalseFormula =
"=COUNTIF(" + isUSColumn.getAddress() + ', "=FALSE")/' + (newCol.length - 1);
let summaryContent = [
[
'=TEXT(A2,"YYYY")',
'=TEXTJOIN(" ", FALSE, "Wk", WEEKNUM(A2))',
countTrueFormula,
countFalseFormula
]
];
let summaryHeaderRow = currentWorksheet.getRange("J1:M1");
let summaryContentRow = currentWorksheet.getRange("J2:M2");
console.log("2");
summaryHeaderRow.setValues(summaryHeader);
console.log("3");
summaryContentRow.setValues(summaryContent);
console.log("4");
let formats = [[".000", ".000"]];
summaryContentRow
.getOffsetRange(0, 2)
.getResizedRange(0, -2).setNumberFormats(formats);
}
}
/**
* Apply conditional formatting based on TRUE/FALSE values of the Is US IP column.
*/
function applyConditionalFormatting(isUSColumn: ExcelScript.Range) {
// Add conditional formatting to the new column.
let conditionalFormatTrue = isUSColumn.addConditionalFormat(
ExcelScript.ConditionalFormatType.cellValue
);
let conditionalFormatFalse = isUSColumn.addConditionalFormat(
ExcelScript.ConditionalFormatType.cellValue
);
// Set TRUE to light blue and FALSE to light orange.
conditionalFormatTrue.getCellValue().getFormat().getFill().setColor("#8FA8DB");
conditionalFormatTrue.getCellValue().setRule({
formula1: "=TRUE",
operator: ExcelScript.ConditionalCellValueOperator.equalTo
});
conditionalFormatFalse.getCellValue().getFormat().getFill().setColor("#F8CCAD");
conditionalFormatFalse.getCellValue().setRule({
formula1: "=FALSE",
operator: ExcelScript.ConditionalCellValueOperator.equalTo
});
}
/**
* Translate an IP address into an integer.
* @param ipAddress: IP address to verify.
*/
function ipAddressToInteger(ipAddress: string): number {
// Split the IP address into octets.
let octets = ipAddress.split(".");
// Create a number for each octet and do the math to create the integer value of the IP address.
let fullNum =
// Define an arbitrary number for the last octet.
111 +
parseInt(octets[2]) * 256 +
parseInt(octets[1]) * 65536 +
parseInt(octets[0]) * 16777216;
return fullNum;
}
/**
* Return the row number where the ip address is found.
* @param ipLookupTable IP look-up table.
* @param n IP address to number value.
*/
function findIP(ipLookupTable: number[][], n: number): number {
for (let i = 0; i < ipLookupTable.length; i++) {
if (ipLookupTable[i][0] <= n && ipLookupTable[i][1] >= n) {
return i;
}
}
return -1;
}
Schritt 5: Führen Sie das Skript aus
Klicken Sie auf die Schaltfläche „Ausführen“ (▶), um das Skript auszuführen.
Geschäftsverbindung
Von diesem Projekt können Unternehmen mit einer Online-Präsenz profitieren, beispielsweise E-Commerce-Shops, Websites und Agenturen für digitales Marketing. Durch die Analyse von Web-Traffic-Datensätzen können Sie Ihren Kunden dabei helfen, die Demografie, Vorlieben und das Verhalten ihrer Zielgruppe zu verstehen. Diese Informationen können Marketingstrategien, die Erstellung von Inhalten und die Website-Optimierung leiten, um das Engagement der Benutzer und die Konvertierungen zu steigern.
Benötigen Sie einen VBA Programmierer?
Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.
